Nested loop (loop over loop)

An outer loop within an inner loop is formed consisting of fewer entries and then for individual entry, inner loop is individually processed.

Select col1.*, col2.* from coll, col2 where coll.col1=col2.col2;

For i in (select * from col1) loop

For j in (select * from col2 where col2=i.col1) loop

Results are displayed;

End of the loop;

The Steps of nested loop are:

1) Identify outer (driving) table

2) Assign inner (driven) table to outer table.

3) For every row of outer table, access the rows of inner table.

Nested Loops is executed from the inner to the outer as:

1) outer_loop

2) inner_loop

3) Hash join

While joining large tables, the use of Hash Join is preferred.

Algorithm of Hash Join is divided into:

1) Build: It is a hash table having in-memory which is present on the smaller table.

2) Probe: this hash value of the hash table is applicable for each second row element.

3) Sort merge join

Two independent sources of data are joined in sort merge join. They performance is better as compared to nested loop when the data volume is big enough but it is not good as hash joins generally.The full operation can be divided into parts of two:

Sort join operation :

Get first row R1 from input1

Get first row R2 from input2.

Merge join operation:

‘while’ is not present at either loop’s end.
if R1 joins with R2
next row is got R2 from the input 2
return (R1, R2)
else if R1 next row is got from R1 from input 1
else
next row is got from R2 from input 2
end of the loop