Page Oriented Nested Loop Join - QUERY OPTIMIZATION

Question

This could be a strange question. I have googled the problem and I couldn't find even the term that I am looking for.

I am learning about QUERY OPTIMATION in DB, and now I am on calculating the COST. I have learnt about Nested Loop Joins which is categorized as

Simple Nested Loop Join (SNLJ)
Page Oriented Nested Loop Join (PONLJ)
Block Oriented Nested Loop Join (BONLJ)

I am ok with SNLJ and BONLJ but I couldn't find any article or tutorial regarding PONLJ. The only think I know about PONLJ is

For each page read from R, get all pages of S, and write out matching pairs of tuples , where r is in R-page and S is in S-page. Cost = M + M*N

I need more explanation on this. Appreciate well described answers.

Thank you.

Maybe this? http://www.dis.uniroma1.it/~catarci/DBslides/Mod5L1LT/sld005.htm — Radim Bača
– Radim Bača, Commented Aug 17, 2018 at 9:16
@RadimBača Thank you for your kind reply. I am unable to find PONLJ there. It contains about SNLJ and BONLJ, but not PONLJ — Ramesh
– Ramesh, Commented Aug 17, 2018 at 9:36
@RadimBača I think you are mentioning about the Index Nested Loops Join. They are different I guess. What do you think? — Ramesh
– Ramesh, Commented Aug 17, 2018 at 10:08

Gordon Linoff · Accepted Answer · 2018-08-17 11:08:33Z

2

This is a bit long for a comment.

I'm not sure what the confusion is -- that is, your short description makes sense to me.

The idea in a nested loop join is that the code cycles through the rows in one table. Then for each row in one table, it looks up matching rows in the second table by comparing each possible pairs of rows.

Normally, you would understand this by looping the the rows individually. But the fact is that databases store rows in other units, particularly on pages.

PONLJ loops through each page in the table and then loops through all the rows on that page. Although this sounds like two loops, it is really going through all the rows only once.

The same process is repeated on the second table.

answered Aug 17, 2018 at 11:08

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ramesh Over a year ago

This sounds good. Can I get any article link or a tutorial where I can understand more...

Collectives™ on Stack Overflow

Page Oriented Nested Loop Join - QUERY OPTIMIZATION

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related