-
hy,
in a 'where' clause ,
the order of join condition is important
or not for performance (in mode= choose or mose =rule)?
And so, how to know the best order (first big table ? or not ?,...)
f.e. :
...where
a.col1 = b.clo1 and
b.col1 = c.col1
is it really the same of :
...where
b.col1 = c.col1
a.col1 = b.clo1 and
or
...where
c.col1 = b.col1
a.col1 = b.clo1 and
or ...?
What about the explain plan in these cases ? is it, all the time , the same ?
thanks in advance
-
Here are the general rules I use.
Large Tables = Top Left
Small Tables = Bottom Right
Are all your join columns indexed?
Do you have good constricting criteria in small tables?
Are your tables analyzed if you're using the cost based optimizer?
Have they been analyzed recently?
Most of all, you know your data's composition, quantity and cardinality. That information alone will help you best in determining how to put together a great running query.
-Ken
-
Originally posted by KenEwald
Large Tables = Top Left
Small Tables = Bottom Right
This is true for RULE based optimisation and NESTED LOOP plans only.
For CBO this rule works not always.
-
In CBO, the order of tables in the FROM clause and joins in the WHERE clause do not matter in 99.9% of the cases. However, for human readability, I follow this convention:
Bring one table at a time into the WHERE clause
Restrict it by every constant/variable available
Join it to every table available
This new table goes on the left of the operator, with the existing tables, constants and variables on the right
Start with the most restricted tables first and save the lookups for last.
Line up all the columns (operands operators operands AND/ORs)
Whatever order of tables I end up with in the WHERE clause, I mimick in the FROM clause.
Following these conventions will make the statements more readable, more maintainable, and possibly more efficient (because joining everything.restricting everything gives the optimizer more chances to get it right, and because that last .1% of the time where the order *does* make a difference, this standard will help)
- Chris
-
Originally posted by chrisrlong
In CBO, the order of tables in the FROM clause and joins in the WHERE clause do not matter in 99.9% of the cases.
U right. I did want to say about it just very carifully.
-
Your responses are very clear !
thanks a lot
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|