when we are using Index nested loop join, is it index on joining column should be only for the inner table or it can be on the inner or outer table?
Advertisement
Answer
A nested loop join is looping through the outer table and then looking up a value in the inner table.
The primary use of an index would be on the inner table — just seeking to the right rows rather than looking for them.
The outer “table” could really be an index scan (which in Oracle assumes that the indexed values are never all NULL). For instance, if an index covers the query, then the original data pages are not necessary.
If indexes are used for both tables, then the join type is an “index-merge” join, rather than a nested loop join.