Disabling hash joins

Tropashko notes that the hash joins may not be the fastest table join method and he removes them  by unsetting the hash_join_enabled parameter and reviews the resulting nested loops table join method:

alter session set "_hash_join_enabled" = false;

You can also turn off hash joins at the session level with "alter session set HASH_JOIN_ENABLED = FALSE"

You can influence the type of join your query will do using the USE_NL (Nested Loop), USE_MERGE (Sort-Merge), and USE_HASH (Hash join) hints.

Many times these are accompanied by the use of the LEADING or ORDERED hint in order to let Oracle know the optimal join order.

The particular query that we are studying in this section has 5 query blocks: 2 pairs of selects from the tables A and B, and one set operation. After this query is transformed, it would consist of only 3 query blocks which we might witness from the QBLOCK_NAME column of the PLAN_TABLE.

Execution plan for comparing the contents of two tables  with hash joins disabled

For more on advanced disabling of hash joins, see Vadim Tropashko's  book "SQL Design Patterns: The Expert Guide to SQL Programming"

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.