Order table in connection: SQL setup
I have a query with 10 tables.
Do I need to follow any order in writing sql connections?
Suppose if table-1 has 1000 records, table-2 has 900 records, and so on.
So I need to write a join for a table that has more records and fewer records follow? so we are improving sql performance.
source to share
Not *. This has not been necessary since Oracle version 6, which had a Rule Optimizer (RBO).
(* unless you are into advanced customization topics and should not use hints as other answers have pointed out and expanded upon)
Oracle 7 onwards has a cost-based optimizer (CBO) that uses statistics to calculate what it thinks will be the most efficient way to execute a query.
RBO support has been removed from 10G onward
source to share
Since oracle 10g has no , you need to put tables in a special order to improve performance.
The optimizer evaluates possible join paths. The maximum is limited by OPTIMIZER_MAX_PERMUTATIONS. But this option is deprecated in oracle 11 .
Order is important if you are working with prompts. Take a 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 above are suitable for oracle older than 10g.
source to share