We know about the various oracle joins . Now we will see how oracle join the tables while executing the queries. There are various join methods in oracle and each one performance differently . Since Performance is key for OLTP system and we should have good information about Join Methods in Oracle to increase performance for sql queries
Here are the Join Methods available in Oracle database
Nested Loops
-For each row in the first row source access all the rows from the second row source.
–Nested Loops Joins are best for OLTP type transactions
-it will be Fastest if rows returned from first table are small
-The oracle 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)
–Hash Join area 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 optimizer 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
They 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
Also Read
Oracle Index clustering factor
SQL trace, 10046 event in Oracle database and trcsess, tkprof utility
How to check Stale statistics
ora-38029: object statistics are locked