Hi!

This blog will look at the USE_**** hints that control the join order in an explain plan.

This blog will focus on these 3 hints:

  • USE_NL(table [table])
  • USE_HASH(table [table])
  • USE_MERGE(table [table])

These are the main hints that control the join order of a SQL statement that has more than 1 table.

The USE_* drives the CBO to use nested loops, merge joins, or hash joins.  I have had good luck at times when Oracle picks the MERGE JOIN operation, changing this behavior to a NESTED LOOP using the USE_NL hint.

The USE_HASH hint will ask Oracle to consider using HASH JOINS and can only be used with ‘=’ conditions in the SQL.

The USE_ hints should list both tables and be used in conjunction with the ORDERED or LEADING hint. IF not…the hint could be considered in one permutation but not another…

 

This is my A B C example query. No hints and it picked the Hash Join operations.

  

Using the USE_NL (nested loop) hint, I can get this to do Nested Loop Operations. Notice that you can list all the tables!

Which one is better? The answer is which one executed quicker.  I say this a lot…SQL tuning is an art, not a science. What sometimes works for one SQL won’t necessarily be the magical fix for the next.

What I want you to get from my blogs and my SQL Tuning class is more things to try and a better understanding of how Oracle works…so…you can code to it. 

Dan

Dan Hotka
Oracle ACE Director
Author/CEO/Instructor