Query Tuning: List of Tables after From Clause
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Query Tuning: List of Tables after From Clause

  1. #1
    Join Date
    Feb 2004
    Mumbai, Maharastra

    Query Tuning: List of Tables after From Clause


    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.


    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.
    Surajit K Mitra

  2. #2
    Join Date
    Jun 2005
    London, UK
    Why do you still need to use the rule-based optimizer?

  3. #3
    Join Date
    May 2005
    Toronto Canada
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width