Results 1 to 3 of 3

Thread: SQL Performance

  1. #1
    Join Date
    Feb 2006

    Unanswered: SQL Performance


    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'


  2. #2
    Join Date
    Aug 2004
    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,

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

  3. #3
    Join Date
    Feb 2006
    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