First page Back Continue Last page Graphics
“New” Nested Loops
Incorporates “batching” for performance
- Fetch “n” rows from outer table
- Sort rows
- Access inner table index
- Keep ROWID’s for matches
- Use ROWID’s to access inner table
Notes:
Lately, I have been seeing a new execution plan for nested loops. Unfortunately, there is no documentation on this variation of nested loops. Tanel Poder (http://www.oracle.com/technology/ocm/tpoder.html) described this process for me. As best I can tell, it goes like this:
Instead of fetching one row from the outer table and accessing the inner table’s index, a batch of rows is fetched from the outer table. The batch size is equal to the arraysize.
This batch is sorted by join key.
The batch is used to search the inner table’s index, finding and saving all ROWID’s where a match is found.
At some point, either after all inner rows are processed, after each batch is processed or some other time, the stored ROWID’s are used to access the inner table.