| Most efficient way to join tables. [message #8722] |
Sun, 21 September 2003 16:11  |
Jesus Marquez
Messages: 1
Registered: September 2003
|
Junior Member |
|
|
Hi,
I don't know what's the right way (from a perfomance point of view) to build a join between tables.
I've been told that the smallest table should be at the very right of the FROM clause
Then, the join between tables should be first in the WHERE clause. After this, all the other restriction should be added.
For instance, we have tables a, b and c, where a contents 1000 records, b 2000 and c 3000.
Is this the most efficient way to build the join sentence?:
select a.*
from c, b, a
where a.id = b.id
and b.id = c.id
and a.limit = 3
and b.something = 25
and c.speed = 2
I've been told that the right way is as follows (joins as last sentences in the where clause):
select a.*
from c, b, a
where a.limit = 3
and b.something = 25
and c.speed = 2
and a.id = b.id
and b.id = c.id
Please, I need some insight on this.
Thank you,
Jesus
|
|
|
|
| Re: Most efficient way to join tables. [message #8723 is a reply to message #8722] |
Sun, 21 September 2003 18:40  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
If you are running under the cost-based optimizer (CBO), the order of the tables in the FROM clause is irrelevant. Under the RBO (rule-based optimizer), the last table in the FROM clause is generally used as the driving table, but not always.
The order of predicates in the WHERE clause does not matter under either the RBO or CBO.
|
|
|
|
| Re: Most efficient way to join tables. [message #8724 is a reply to message #8722] |
Sun, 21 September 2003 21:25 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
When using Rule Based Optimiser, the driving table is determined based on the order of tables appearing after the FROM clause(ie from RIGHT to LEFT usually).
When you use Cost Based optimiser ,it determines the join order based on a number of factors such as presence of indexes,optimizer* init.ora values,the size of tables,HASH_JOIN availability,selectivity and so on.
When you join 3 tables, the CBO can pick up the join order from 3! (ie 6 ) choices as it seems fit.
The CBO will most often pick up the best choice based on the statistics,but remember that it optimises for 'throughput' or 'ALL_ROWS' by default and hence goes for sort_merge joins or hash_joins usually(but not always) and thus favouring full table scans over Index scans.Again this is affected by other factors such as db_file_multiblock_read_count ,OPTIMIZER_INDEX_COST_ADJ etc.
So you might need to 'hint' the CBO with FIRST_ROWS or USE_NL (ie Nested loops) for 'response time' .
The same with the join order.If you find a specific order yielding faster throughput (or response), you will have to provide 'ORDERED' hint to join the tables in the order they appear in the FROM clause(ie LEFT TO RIGHT)
IF you are sure about the driving table,but not sure about the rest of the tables, you can use LEADING hint to indicate to the CBO,which one to start with.
There is no hard and fast rule about join order. For eg, if you are doing a sort merge join ( for throughput) , the join order is totally irrelevant as every table has to be sorted in full and merged.
In general,the goal would be to eliminate as much unwanted data earlier as possible by starting with the table that is more limiting(say high index selectivity) that can be used in the WHERE clause.
As far as your WHERE clause,the order of the predicates do not dictate the join order.The predicates with the indexes are usually evaluated first. There is ORDERED_PREDICATES hint that can be used to specify the order of evaluation of the predicates,when there are no indexes. They will hint the optimiser to evaluate from top to bottom.
In your case,you seem to have limiting conditions on all the 3 tables.You can autotrace your queries and see what the CBO does.
Hope this helps
Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
