There are 3 main Join Methods in Oracle

• Nested Loop Join
• Sort Merge Join
• Hash Join

Nested Loop Join

Nested loop Join is similar to looking up a number in the phone book. Typically we use the index to find the starting alphabet and then look up the name.

Oracle Nested Loop Join

• Oracle reads the first row from the first row source and then checks the second row source for matches
• All matches are placed in the result set  and Oracle goes on to the next row from the first row source
• This continues until all rows in the first  row source has been processed
• The first row source is often called the outer table and the second row source is often called the inner table
• Smaller result set should be the driving table
• larger result set should be the inner table

When do you Use nested loop joins

• Driving row source is small and the joined columns of the inner row source are uniquely indexed.
• This is ideal for query screens where an end user can read the first few records retrieved while the rest are being fetched
• typically for OLTP queries.

When Not to Use

Nested loops joins can be very inefficient  if the inner row source does not have an index on the joined columns or if the index is not highly selective.

Hash Joins

Oracle Hash Joins

• Hash Joins are used for joining large data sets  (data warehouse, batch)
• The optimizer uses the smaller of the two tables to build a hash table, based on the join key , in memory
• It then scans the  larger table, and performs the hashing algorithm on the join columns
• It then probes the previously built hash table for each value and if they match, it returns a row
• It needs 1 meg + hash area  size
• works only for equi joins

Hash join Equi Join Query

Oracle hash join equi join explain plan

Notice that it is doing a hash join by creating a hash table on the product table and then it created a hash key for the sales_history product_ID column, then it is using that product_id column to scan through the hash table build for the product table. This hash join will not work if we change the “equality” in the where clause to “<>”

Oracle hash join changed to nested loop with the in equality (<>) operator

Sort Merge Join

Sort merge joins are useful when the join conditions between tables is an inequality condition such as <=, >=, >,< . Sort merge joins can do better than nested loop joins for large data sets.

The join consist of two steps:

• Sort Join Operation: Both the inputs are sorted on the join key.
• Merge Join Operation : The sorted lists are merged together

A sort merge join is more likely  to be chosen if there is an index  on one of the tables that will eliminate one of the sorts

Oracle tuning Sort Merge Join SQL with Less than operator

Oracle tuning Sort merge join execution plan

As you can see it has sorted the product table and the sales_history table and then merged them using the merge join.

Please note that sorting is very expensive for oracle database. So unless it’s absolutely necessary Oracle will not use “SORT MERGE” join.

1. First preference for Oracle is Nested Loop Joins
2. Hash joins if the tables are big
3. if there is an in equality condition then it goes for the sort merge join

For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)

Alternatively click through the link  if you found this article interesting. (This will help  elevate my companies Search engine rankings)