Question: How do I disable sort
merge and hash joins?
Answer:
Vadim Tropashko's is the author
of "SQL
Design Patterns: The Expert Guide to SQL Programming", the definitive
reference for SQL design patterns, a critical knowledge area for any Oracle
developer.
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;
alter session set "_optimizer_sortmerge_join_enabled" = false;
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 sort
merge disabled
