First page Back Continue Last page Graphics
Outer Nested Loops
Outer join forces choice of driving table
Table being preserved must by driver
Can hurt performance
Notes:
Because of the way it is processed, the use of an outer join forces the optimizer to chose the driving table (i.e. outer join limits the possibilities). With an outer join, Oracle must use the table whose rows are being preserved (the BIG table in this example) as the driving table. For each row read from the driving table, it searches the other table (possibly via an index) looking for matches. If a match is found, the joined row is returned. If no match is found, the driving table’s row is returned anyway, with NULL for all fields in the other table.
If Oracle were to chose the SMALL table as the driving table in this example, then after processing all rows in the SMALL table, how would Oracle know which rows in the BIG table had not been matched so those rows could then be returned? Oracle must use the BIG table as the driving table in the join, even though performance suffers.
So, the moral of the story is that an outer join can hurt the performance of a nested loop. Use caution.
Refer to the supplied files nested_loops.sql, nested_loops_trace.txt and nested_loops_10053.sql for a demonstration of this and other nested loops concepts.