Results 1 to 3 of 3

Thread: SQL Performance

  1. #1
    Join Date
    Feb 2006
    Posts
    13

    Unanswered: SQL Performance

    Hello,

    Is there a difference in speed of execution between the following two sql queries on Oracle?

    Suppose we have one table with a non-primary index (field1, field2, field3).

    SQL1: select * from table where field1='value' and field2='value' and field3='value'

    SQL2: select * from table where field3='value' and field2='value' and field1='value'

    Thanks!

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Independently from the fact that you have an index on (field1, field2, field3)., it depends on what type of optimizer you use.

    With the RBO (rule-based optimizer), the order of predicates is important, so the two queries you are referring to could have lead to two different execution plans.

    With the CBO (cost-based optimizer), the order of predicates has no importance at all, so the execution plan would be the same. However, the order of fields within the index is important and may lead to different execution plans : in general putting more selective fields first is more efficient (provided you do query upon these fields ). If you feel like an index should be used but the CBO doesn't use it though statistics are up-to-date, then you should consider changing the order of fields within the index, update stats and see what happens.

    The RBO is available up to 9iR2, deprecated since 10gR1.
    The CBO exists since 8i I think and is the only one available since 10gR1.

    HTH & Regards,

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

  3. #3
    Join Date
    Feb 2006
    Posts
    13
    Thanks for the well structured answer! Very clear now

Posting Permissions

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