Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle Hash Join Right Semi tips

Expert Oracle Database Tips by Donald BurlesonDecember 7, 2015

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.



Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster