Optimizer Access Paths & Join Operators ( Hash Join, Nested Loop ) in Oracle | Oracle Database Performance Tuning Tutorial -8


I will explain Optimizer Access Paths & Join Operators in this article.



Oracle Join Operators


Read the following article before this, if you want to learn more detail about Optimizer Access paths.

Optimizer Access Paths (Table and Index Scan) in Oracle | Oracle Database Performance Tuning Tutorial -7




A Join operation is a method of Combining data from two or more table,views and it defines the relationship between tables,views as follows and  returns one resulting row source.





Join operation types are as follows.

  • Join, (Equijoin/Natural – Nonequijoin)
  • Outer join , (Full, Left, and Right)
  • Semi join, EXISTS subquery
  • Anti join, NOT IN subquery
  • Star join


Join (Equijoin/Natural – Nonequijoin):    it returns rows which match predicate join
Outer join (Full, Left, and Right):   it returns rows that match predicate join and row when no match is found
Semi join (EXISTS subquery):   it returns rows that match the EXISTS subquery. Find one match in the inner table, then stop search.
Anti join (NOT IN subquery):   it returns rows with no match in the NOT IN subquery. Stop as soon as one match is found.
Star join:    This is not a join type, but just a name for an implementation of a performance optimization to better handle the fact and dimension model.


There are 3 Join methods for Join operators of Oracle optimizer as follows.

  • Hash join
  • Nested loops
  • Sort-merge join



Oracle Hash Join

Hash join is performed only for equijoins and most useful for joining large data sets ( Large Tables, Views ).

Oracle optimizer uses the two small tables to build a hash table on the join key in the memory, then it scans the larger table and probing the hash table to find the joined rows.

The execution step of Hash join is as follows



You can use USE_HASH hint in the SQL Statement to instruct the optimizer for using Hash join when joining large tables.



Oracle Nested Loops Join

Nested Loops join is most useful for joining small data sets ( SmallTables, Views ), if the join condition is appropriate for accessing the second table.



You can see the Nested loop join and its types in the execution plan as follows.


You can use USE_NL hint in the SQL Statement to instruct the optimizer for using Nested Loop join


Oracle Sort-merge join

Sort-merge join is used to join rows from two independent sources (Tables). All row sources are sorted by the same sort key, then sorted rows are merged as follows.




If one or more row sources are already sorted, then sort merge join is prefered instead of hash join, if not Hash join is generally better than sort merge join.


If the join condition of tables is an inequality condition  ( such as <, <=, >, or >= ) and sorting is required, then Oracle optimizer can select the Sort merge join instead of Hash join for joining large data sets ( Large Tables, Views ).


You can use USE_MERGE hint in the SQL Statement to instruct the optimizer for using Sort merge join.



Oracle Cartesian Join

It is used when any join conditions don’t exist between tables. Cartesian product is created between two data sources, and Optimizer joins every row from one source with every row from the other data source as follows.


I will continue to explain Performance tuning tutorial in the next articles.



Next post link about Performance Tuning Tutorial is as follows.

What is the SQL Tuning Set and How to Create the SQL Tuning Sets in Oracle | Oracle Database Performance Tuning Tutorial -9




Do you want to learn Top 30 SQL Tuning Tips and Tricks, then read the following articles.

SQL Tuning Tips and Tricks Tutorial in Oracle -3

 3,921 views last month,  2 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *