order in join condition

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

Quote:

---

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

Quote:

---

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 :) :)