Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    19

    Unanswered: Query Optimisation

    I read this after some google searching

    Oracle uses the following steps to evaluate the order of SQL predicates:
    1. Subqueries are evaluated before the outer Boolean conditions in the WHERE clause.
    2. All Boolean conditions without built-in functions or subqueries are evaluated in reverse from the order they are found in the WHERE clause, with the last predicate being evaluated first.
    3. Boolean predicates with built-in functions of each predicate are evaluated in increasing order of their estimated evaluation costs.
    I just ran a couple of tests on a DB and it appears to work in the opposite fashion from what this suggests.

    I started by running this:

    Code:
    create table  Test2 as
    select * from TEST
    where column1 is not null
    and rownum <= 1
    and rownum <= 23044141;
    Which took less than a second, then I ran this:

    Code:
    create table Test3 as
    select * from TEST
    where column1 is not null
    and rownum <= 23044141
    and rownum <= 1;
    Which took over a minute.

    Can anyone explain this a little more clearly to me as I am really stumped by this.

    Thanks in advance
    Connor

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Oracle uses the following steps to evaluate the order of SQL predicates:
    Is version dependent.
    What was true in the past may no longer be true today.

    The only thing that matters is how how YOUR version behaves on YOUR system.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2009
    Posts
    19
    If I tell you that I'm using 10.2.0.4.0 - 64bit can you give me anything more... or as I suspect is this really just a system specific thing?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I suspect is this really just a system specific thing?
    It is Oracle version dependent.
    With each release the Cost Based Optimizer (CBO) gets smarter (in theory).
    Some SQL behavior can change significantly from 1 version to the next.
    EXPLAIN PLAN will show what is true for you for today.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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