Various Joins Method in Oracle



Last updated on September 15th, 2016 at 05:24 am

Performance is key for OLTP system and we should have good information about Joins Method in Oracle to increase performance for sql queries

Here are the  Joins Method in Oracle available in Oracle database

Nested Loops
-For each row in the first row source access all the rows from the second row source.
-Best for OLTP type transactions
-it will be Fastest if rows returned from first table are small
-The optimizer first determine the driving table and designates it as the outer loop.This is the driving row source. It produces a set of rows for driving the join condition. The row source can be a table accessed using index scan or full table scan. The rows can be produced from any other operation too. For example the output from a Nested Loop Join can be used as a row source.
-The optimizer designate other table as inner Loop.This is iterated for every row returned from the outer loop. This is an access operation on a table and ideally should be an index scan.
-Operation performed by INNER table is repeated for every row returned in OUTER table
-If the optimizer is choosing to use some other join method, you can use the USE_NL(A B) hint, where A and B are the aliases of the tables being joined.

Plan
————————————————-
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS FULL OF OE_ORDER_LINES_ALL
TABLE ACCESS BY INDEX ROWID OE_LINES_ALL
INDEX RANGE SCAN OE_LINES_N1

Nested Loop outer join
-Similar to nested loop
-Rows returned even if inner loop does not have any rows meeting the criteria
-Unlike the nested loop which might be driven from either of the tables, this is one way join
a = b(+) will always go to a before b, this may result in more expensive plan (possibly non-NL)
(+) always goes on deficient side

Sort Merge Join
-Rows are produced from first table and are then sorted
-Rows are produced from second table and sorted by the same sort key as first table
-Table A and B are NOT accessed concurrently
-Sorted rows from both sides are then merged together(joined)
-No concept of driving table – order cannot affect outcome
-Faster than Nested loops if:
rows produced from table 1 are large
access to table 2 requires a large range scan for each row in table 1
-To instruct the optimizer to use a sort merge join, apply the USE_MERGE hint. You might also need to give hints to force an access path.
-There are situations where it is better to override the optimize with the USE_MERGE hint.

Execution Plan
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL OF OE_ORDER_LINES_ALL
SORT JOIN
TABLE ACCESS FULL OF OE_HEADERS

Hash Join
-Smallest table is passed over and a hashing algorithm is applied to each row to create a hash table in memory.
-Second table is passed over and the same hashing algorithm applied to check for matches (ie. joins)
-Faster than sort merge join:
sort operation required by sort merge join can be expensive if tables are large and not in any matching order
-Apply the USE_HASH hint to instruct the optimizer to use a hash join when joining two tables together.
-Ensure that hash_area_size is large enough to hold the smaller table in memory. Otherwise, Oracle must write to the TEMP tablespace, slowing down the hash join

Plan
————————————————-
SELECT STATEMENT
HASH JOIN
TABLE ACCESS FULL OE_ORDER_LINES_ALL
TABLE ACCESS FULL OE_HEADERS

Cartesian Join-Are generally expensive as the result is the Cartesian product of the two tables.
-Can result from 1 or more of the tables not having any join conditions to any other tables in the statement
-Can occur even with a join.

Plan
————————————————-
SELECT STATEMENT
SORT UNIQUE
MERGE JOIN CARTESIAN
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID OE_ORDER_LINES_ALL
INDEX RANGE SCAN OE_ORDER_LINES_ALL_N1
TABLE ACCESS BY INDEX ROWID OE_HEADERS
INDEX RANGE SCAN OE_HEADERS_N1
SORT JOIN
INDEX FAST FULL SCAN OE_HEADERS_N1

Hope you like this post on Joins Method in Oracle. Please do provide the feedback


Leave a Reply