Question: What is the "HASH JOIN RIGHT SEMI" SQL
execution plan? I am using an EXISTS clause, and I see poor
performance in Oracle 10g.
Answer:
In general, a semi join is used for an in and
exists clause, and the join stops as soon as the exists
condition is satisfied. The Oracle optimizer will convent an in
clause to an exists clause internally, so they are
identical in function, but the optimizer cannot perform a hash semi
join if the subquery contains a distinct or union
clause. See MOSC Note 144967.1.
The HASH JOIN RIGHT SEMI, is a
hash join
plan used with where exists clauses to speed up the
discovery of one matching condition.
It is called a semi-join because even if duplicate rows are
returned in the subquery, only one set of matching values in the
outer query is returned. In the case of the exists clause,
the subquery is executed, but even if the subquery returned multiple
rows, the semi-join will not duplicate the value referenced in the
outer query.
In some cases where there is not enough PGA to perform a hash
join, the CBO may invoke a nested loops table access method
for all queries with exists clauses, but only when an index
is available on both join columns.
If the prerequisite conditions are correct (no distinct
clause), you can force a hash semi join with the hash_sj
hint. The hash_sh hint is inserted into the exists
subquery. The hash_sj hint converts the exists
subquery into a special type of hash join between t1 and t2 that
preserves the semantics of the subquery. That is, even if there is
more than one matching row in t2 for a row in t1, the row in t1 is
returned only once.
Also note that the HASH JOIN RIGHT SEMI is eligible to use
parallel query.
The Oracle SQL optimizer is supposed to un-nest all
exists clause operators, and it is always a good practice to
rewrite a standard join form of an exists test into a real
exists clause. In rare cases (and early releases of
10g), the optimizer may compute the wrong cardinality for a hash
right semi join. Another testing option if you suspect an
optimizer problem is to use parallel query using the
when clause to manually un-nest the sub-query. This test works
best when you suspect that your hash join semi is
inefficient and the target of the subquery is a small table.
More on tuning SQL subqueries is available
HERE.
See these notes on
replacing the exists clause, in certain cases.
Note that there is also
a bug in Oracle 10g relating to subquery un-nesting, un-nesting
exists clauses into hash join semi operations, choosing a
more expensive nested loops plan. The hash join right semi
execution plan step is fix in Oracle 11g and beyond.
 |
|
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.
|
