Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2007
    Posts
    4

    Unanswered: Order of query evaluation

    Hi ALL

    I have 2 run a query on 10 million records and I have a index on id field and I have to select records where id >1 and id <10000.
    Now I have several conditions in the where clause so I should use the condition on id first in the where clause or in the last position in the where clause

    as

    where id >1 and id <10000 and .......................
    or
    where ...................... and id >1 and id <10000

    as the condition on id will limit the records which are to be processed in the query.

    Thanks
    TK

  2. #2
    Join Date
    Jul 2007
    Posts
    4
    Please note that the database concerned is ORACLE

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it should not make any difference
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by r937
    it should not make any difference
    Right, as far as we're speaking of the CBO (Cost-Based Optimizer)... However, if the Rule-Based Optimizer (RBO) is used, I think it might give different execution plans... But some people here would be of better help than me if this is the case .

    The RBO is dead in Oracle 10g, but was still there in Oracle8i/9i/9iR2 and was the only one in older versions.

    Which version of Oracle are you using ?

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    Jul 2007
    Posts
    4
    Hi rbaraer,

    It is Oracle 8i. So please tell if the id condition should come first or last, In which order the predicates in the where clause are executed Right to Left or Left to right ???

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    OK, I'm sure some people here will help you more than I can do, for I've never worked with 8i and I can't find the 8i documentation...

    The 9i documentation concerning the RBO can be found here. You will find how to optimize your query using the RBO on this page...

    From 8i on, the parameter OPTIMIZER_MODE tells Oracle whether to use the RBO or the CBO, and has several modes for the CBO (FIRST_ROWS, ALL_ROWS...). The default mode in 9i is CHOOSE, which tells Oracle to use the CBO as soon as statistics are gathered, but use the RBO if no statistic is present...

    AFAIK, provided you gather stats, you will use the CBO and you should not worry about the order of predicates in the WHERE clause. The drawback is that from what I've heard (take this with a lot of precautions ), the CBO was quite weak in 8i, so that a lot of people preferred to keep on using the RBO until 9i or 9iR2. Not sure if they were right or not .

    That's it for what I know. You can always try to gather stats and play with the OPTIMIZER_MODE parameter inside your session (with ALTER SESSION) to see what works better.

    BTW I think 8i is deprecated now (not supported anymore)... thought of upgrading to 10gR2 ? It's much more easy to tune .

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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