USE_MERGE and NO_USE_MERGE

USE_MERGE Hint Description

When you use the USE_MERGE hint, it directs the optimizer to perform a sort-merge join when combining each specified table with another row source.   A sort-merge join is particularly efficient when both inputs to the join are sorted on the join key, either inherently or through sorting during query execution. 

Example

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

  FROM myTab1, myTab2

  WHERE myTab1.col1=Mytab2.col1

/

NO_USE_MERGE Hint Description

The NO_USE_MERGE hint tells Oracle to avoid using merge joins.  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

/

Notes

The following page gives more information on sort-merge-joins

Published on 

Published 26th November 2024