Tuning Table join in Oracle

      is the recomnendation i give. 

Join tables to query in oracle is the most common manipulation that people do in their daily work. However, most of those slow queries or slow applications are due to the some bad joined tables . As software engineer, most of them don't know how to check how their query goes . It's ok. I have learned some when i studied and worked before.

I have summarise some notes to share with you how to tune your query in your work.

Oracle Join types :

• Nested loops join
• Hash Join
• Merge Sort Join

Nested loop join : loop to find and match one by one . It's used in small range scan in OLTP app.

Merge sort/Hash Join : Combine 2 tables to find and match , throw aways the leftover. It is used in massive range scan in OLTP application.

Check number of visit :  set statistic-level = all 

Under nested loop join : 

Starts : how many times this table is visited , very important param

Under Hash join/Merge sort join : The number of visit is alwayse 0 or 1. 

Table sequence and efficiency

When you notice, small table size in nexted loop join should be put before, the bigger table size one should be  put behind.  So  you use small table to find the big table , the number of visit will be less. 

But for Hash Join and Merge join, this doesn't make a difference. 

Table join is ordered ?

Nested loop join and hash join will not sort / order the result (0 sort), Merge sort will do sorting twice and merge in the end. 

Join Limitation ?

Nested loop has no limitation , For Hash join, you can't use < or > or like , only can use = , For Merge sort join, you can't use != , or like . but you can use others like < or >. 

Others :

Nested loop join : create an index in the constraint column where n = 2 ( create index here) , create an index in the constraint of connected table , where t1.id = t2.id ( create an index)

Hash Join : create indexes in all linking constraints in both tables.

Merge sort join : the same as hash join. if the table is visited tons of times , please consider to choose hash join as this will reduce the cost or sortting .