Query Tuning: List of Tables after From Clause

Hi,

Can anyone put some light on the way the list of tables that should be stated after the FROM clause in a SQL Query.
Since, performance of a query always differs if proper flow of tables are not listed in the FROM clause.

Eg.:

Case 1:
select a.* from a, b where a.id = b.id;

Case 2:
select a.* from b, a where a.id = b.id;

In both the above cases the performance of the query would be different, since after the FROM clause the way the tables are referred are different in both the queries.

Please help in letting me know the CONVENTION of listing tables after the FROM clause.

Why do you still need to use the rule-based optimizer?

While I basically agree with William, if you must use RBO (as I do to keep vendor support on an application), then ther are a couple of things to keep in mind writing your queries.

In your example of a simple join with no other conditions, you want to list the smallest table (i.e. the one you want to drive the nested loop) last. The RBO will full scan the last table listed and use the index on the id column of the other table to access the rows.

If there are predicates in the WHERE clause other than join conditions (this is true for single table queries as well), and there is more than one possible index access path using those predicates, you want to list the most selective predicate(s) last, since RBO seems to evaluate the predicates from the bottom up. In a single table query, this will force RBO to use the "best" index. In a join query, this will force RBO to use the tablewith the most selective predicates as the driver.

But really, if you can analyze your tables and indexes and let the CBO worry about all that stuff.

HTH
John