USE_HASH and NO_USE_HASH

USE_HASH Hint Description

When you use the USE_HASH hint, it directs the optimizer to perform a hash join when combining each specified table with another row source.   A hash join is particularly efficient for large unsorted datasets. 

Example

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

  FROM myTab1, myTab2

  WHERE myTab1.col1=Mytab2.col1

/

NO_USE_HASH Hint Description

The NO_USE_HASH hint tells Oracle to avoid using hash joins.  This means your query is typically more likely to use a nested loop or a merge 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 hash joins

Published on 

Published 29th November 2024