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

Thread: The Order of Joins in a Query...

  1. #1
    Join Date
    Feb 2001
    Posts
    184
    Hi,

    Does any one have idea to have the Queries be in a proper Order.

    Like Select c1,c2,c3,c4 .. Which Columns to be Selected 1st.
    From t1, t2,t3,t4,t5.. Which table to be used first .. Larger or Smaller and same in Where
    Where t1.a=t2.a and t2.b=t3.c etc... Which order for this clause, Bigger tables first or What? and Does it make any difference if you Bigger column Left of the Equation and smaller column on the Rights.

    Please elaborate the best Order to Construct the Query.
    I know the Queries are read Bottom Up and the most restrictive conditions should be the last in where clause.

    Thanks..

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    If you are using the CBO - and you should be - then none of that matters :). You want to write the statement so that it is as organized and humanly-understandable as possible. How you structure the statement is important to Oracle (whether to use an IN or a correlated sub-select, etc.), but how you type it in is pretty much irrelevant.

    - Chris

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Since this is a vast area to discuss in detail on this forum, I would suggest that you check the metalink's technical libraries on "Databas and SQL Tuning" which discusses this in detail. It is under Servers title.

    Good luck;
    Sam

    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Feb 2001
    Posts
    184
    Thanks any way.

    We can some suggestion on this forum as well.
    Whole Database is Analyzed and Statistics are there. Using the Rule based Optimizer.

    Please any suggestion of the Order of any Select Statement, let's say that Equi Join of 10 Tables.

    Thanks

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hmm what´s the point of analyzing and gather the statistics if you are using RBO...?

  6. #6
    Join Date
    Feb 2001
    Posts
    184
    Sorry MisTyped... Using only the Cost based Optimizer.

    I apologize ....

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