SQL Tuning : Join order

• Table join optimization
• Histograms

Setting histograms on columns in where clause join can help Optimizer to choose optimal join order

• Transitive clausure

if a=b and b=c, therefore a=c

select * from a,b,c where
a.id=b.id and b.id=c.id
select * from a,b,c where
a.id=b.id and b.id=c.id and a.id=c.id

• Ordered hint

The first table that appear  the from clause is the driving table, often used when 4 or more tables are involved in join. It can save a huge  amount of parse time.

select /*+ ordered use_hash(emp, dept) use_nl (sal, bonus) */
from
  emp,
  dept,
  sal,
  bonnus
where ....

• orderered_predicates hint

CBO  typically evaluates  SQL predicate following 3 rules :
a) Subqueries are evaluated before outer Booleans conditions
b) Booleans conditions lacking (subqueries or built in-fonction) are evaluated in their order in where clause
c) Boolean conditions using build in-function are evaluated according to their costs

There ordered_predicates overrides these rules, allowing the where clause items to be evaluated in the order that they appear in the query.