NestedLoopJoin

NestedLoopJoin, the simplest join strategy, is a binary operator with the left child forming the outer data stream and the right child forming the inner data stream. For every row from the outer data stream, the inner data stream is opened. Often, the right child is a scan operator. Opening the inner data stream effectively positions the scan on the first row that qualifies all of the searchable arguments. The qualifying row is returned to the NestedLoopJoin’s parent operator. Subsequent calls to the join operator continue to return qualifying rows from the inner stream. After the last qualifying row from the inner stream is returned for the current outer row, the inner stream is closed. A call is made to get the next qualifying row from the outer stream. The values from this row provide the searchable arguments used to open and position the scan on the inner stream. This process continues until the NestedLoopJoin’s left child returns End Of Scan.

1> -- Collect all of the title ids for books written by "Bloom".
2> select ta.title_id
3>       from titleauthor ta, authors a
4> where a.au_id = ta.au_id
5>       and au_lname = "Bloom"
6> go
	
QUERY PLAN FOR STATEMENT 1 (at line 2).

3 operator(s) under root

The type of query is SELECT.

ROOT:EMIT Operator

	|NESTED LOOP JOIN Operator (Join Type: Inner Join)
	|
	|   |SCAN Operator
	|   |  FROM TABLE
	|   |  authors
	|   |  a
	|   |  Index : aunmind
	|   |  Forward Scan.
	|   |  Positioning by key.
	|   |  Keys are:
	|   |    au_lname ASC
	|   |  Using I/O Size 2 Kbytes for index leaf pages.
	|   |  With LRU Buffer Replacement Strategy for index leaf pages.
	|   |  Using I/O Size 2 Kbytes for data pages.
	|   |  With LRU Buffer Replacement Strategy for data pages.
	|
	|   |SCAN Operator
	|   |  FROM TABLE
	|   |  titleauthor
	|   |  ta
	|   |  Using Clustered Index.
	|   |  Index : taind
	|   |  Forward Scan.
	|   |  Positioning by key.
	|   |  Keys are:
	|   |    au_id ASC
	|   |  Using I/O Size 2 Kbytes for data pages.
	|   |  With LRU Buffer Replacement Strategy for data pages.

In this example, the authors table is being joined with the titleauthor table. A NestedLoopJoin strategy has been chosen. The NestedLoopJoin operator’s type is “Inner Join.” First, the authors table is opened and positioned on the first row (using the aunmind index) containing an l_name value of “Bloom.” Then, the titleauthor table is opened and positioned on the first row with an au_id equal to the au_id value of the current authors’ row using the clustered index “taind.” If there is no useful index for lookups on the inner stream, then the optimizer may generate a reformatting strategy.

Generally, a NestedLoopJoin strategy is effective when there is a useful index available for qualifying the join predicates on the inner stream.