IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
Hi,
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.
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
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.
