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
    Location
    Mumbai, Maharastra
    Posts
    67

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

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

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

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