Ordering table in join : SQL Tuning

Question

I having a query with 10 tables joined.

Do i need to follow any order in writing the sql joins?

Suppose if table-1 has 1000 records, table-2 has 900 records and so on

So do i need to write the join for the table has higher number of records and followed by lower number of records? so that we improve the performance of sql.

Answer 1

No*. That's not been necessary since Oracle version 6 which had a rule based optimiser (RBO).

(* unless you're getting into advanced tuning topics and need to use hints, as other answers have pointed out and expanded on)

Oracle 7 onwards has a cost based optimiser (CBO) which uses statistics to calculate what it believes will be the most efficient manner to execute the query.

Support for the RBO has been removed from 10g onwards

See: http://www.dba-oracle.com/oracle%5Ftips%5F10g%5Fcbo.htm

Answer 2

Since oracle 10g there is no need to put the tables in a special order to improve performance.

The optimizer is evaluating possible the join paths. The maximum is limited by OPTIMIZER_MAX_PERMUTATIONS. But this parameter is obsolete in oracle 11.

The order is important if you work with hints. Look at the following links for further reading.

• http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/optimops.htm#i76330
• http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/hintsref.htm#i8327

All answers give above are right for oracle older than 10g.