USE_NL and NO_USE_NL

USE_NL Hint Description

When you use the USE_NL hint, you are telling the optimizer to perform nested loop joins.  By applying this hint to a specific table, you are telling the optimizer to treat that table as the inner table in the join 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 myTab2) */ col1 

  FROM myTab1, myTab2

  WHERE myTab1.col1=Mytab2.col1

/

NO_USE_NL Hint Description

The NO_USE_NL hint tells Oracle to avoid using Nested Loops.  This means your query is typically more likely to use a hash join.

Example

SELECT /*+ NO_USE_NL(myTab1 myTab2) */ col1 

  FROM myTab1, myTab2

  WHERE myTab1.col1=Mytab2.col1

/

Published on 

Published 8th November 2024