do not understand NARY NESTED LOOP JOIN explanation in queryprocessing documentation

[Former Member]

Hi all,

I'm actually reading the book 'Performance and Tuning Series : Query processing and Abstract plans' from ASE 15.7

My english might not be that good but I don't understand the explanation about the NARY NESTED LOOP JOIN

"With a series of NESTED LOOP JOIN, a scan may eliminate rows based on searchable argument values initialized by an earlier scan. That scan may not be the one that immediately preceded the failing scan. With a series of NESTED LOOP JOINs, the previous scan would be completely drained although it has no effect on the failing scan. This could result in a significant amount of needless I/O. With NARY NESTED LOOP JOINs, the next row fetched comes from the scan that produced the failing searchable argument value, which is far more efficient."

Could anyone help me understanding the NARY NESTED LOOP JOIN?

Thanks

Simon

[Former Member]

Thanks Mark and Alexandre for your answers

It was definitely helpfull.

Best,

Simon

[Former Member]

As basically i could explain, Nary NLJs happen with 3 or more tables(or indexes).

On a series of 3 and more standard Nested Loop Joins connected, the 3rd table-set onwards are read multiple times since the beggining of its data(or index) for each step of the previous loop on the 2nd table(or index).

Nary NLJs avoid the Multiple lookups on the 3rd and onwards tables(indexes) by stablishing a relation between the 2nd and subsequent tables(indexes) and then they are read in one loop at a time, instead of multiple of multiples loops , like would happen on a cascaded NLJ

Hope it helps...  im low in time to write an example