DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: order in join condition

  1. #1
    Join Date
    May 2001
    Posts
    22

    Unhappy

    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

  2. #2
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    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
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  6. #6
    Join Date
    May 2001
    Posts
    22
    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
  •  


Click Here to Expand Forum to Full Width