USE_NL_WITH_INDEX

USE_NL_WITH_INDEX Hint Description

When you use the USE_NL_WITH_INDEX hint, you are telling the optimizer to perform nested loop joins.  More specifically, by applying this hint to a specific table and index, you are telling the optimizer to treat that table and index as the inner object in the join operation with another table. This can be very useful if you know that a nested loops join would be more efficient for your query.  For example, when joining small tables or when indexing supports fast lookups.

Example

SELECT /*+ USE_NL(myTab1 myInd1) */ col1 

  FROM myTab1, myTab2

  WHERE myTab1.col1=Mytab2.col1

/

Notes

• If no index is specified in the hint, then the optimizer will pick a suitable index if one exists.  If not suitable index exists the hint will be ignored.

• A suitble index, is an index that has at least one join predicate in the index key.

Published on 

Published 26th November 2024