JL Computer Consultancy

Addendum: 13^th March 2005:

Following an email from Gabe Romanescu who had run the test scripts on Oracle 9.2, I have revisited this page to point out that it is now out of date. From Oracle 8.1, the cost based optimizer changed the underlying order of evaluation from bottom-up to top-down (as the final strategy after all other special considerations have been considered).

The rule-based optimizer still runs bottom-up, of course – it hasn’t been changed since Oracle 7.

For Oracle 9, though, a more interesting development has appeared, whereby the optimizer will consider the cost of all outstanding predicates, and re-order them to minimize CPU cost. There is a short article about this that I wrote for OTN.

A recent (May 1999) item on the comp.databases.oracle.server newsgroup asked 'What is the order of execution of the predicates in a where clause in the case where there are no indexes have been considered?' For example in the query:

        Select  count(*)

        From    tab1

        where

               col1 = 'abc'

        and     col2 like '%xyz%'

where neither col1 nor col2 had any indexes and the query necessarily had to run a full table scan, would Oracle be smart enough to note that the prdicate on col1 was much more selective than the predicate on col2, and therefore do the test of col1 first ?

My previous testing on Version 7 had indicated that the answer was NO. For version 7 Oracle works on the predicates from the bottom up. I had assumed from some recent experiences that the answer for Oracle 8 was also 'bottom up', but since the optimiser may have changed I thought I would check it properly.

For your entertainment (and so you have a chance to prove me wrong) the attached is the method I used.

Step 1 - write a function that can be called from SQL - the function MUST access the database..

Step 2 - Create a table which can be queried on two columns without using an index - make the columns have very different data distribution characteristics

Step 3 - Write two versions of a query with the WHERE clause re-ordered.

Step 4 - Trace the execution of the query to see how many times the function is called.

The function:

rem

rem     c_pack.sql

rem

rem     Little package to test order in where clause

rem     The function accesses the database for no good reason

rem     then returns the value 1. 

rem

create or replace package where_test_p as

        function where_test return number;

        pragma restrict_references(where_test,wnds, wnps, rnps);

end;

.

/

create or replace package body where_test_p as

function where_test return number is

        v_junk varchar2(20);

begin

        select user into v_junk from dual;

        return 1;

end;

end;

.

/

Creating the data set

rem

rem     c_tabs.sql

rem     Create tables for WHERE clause demo

rem

rem     Then analyze them so that Oracle knows that one 

rem     has far fewer rows per value than the other.

rem

rem     Col1 has the values 0 to 999, occuring once each

rem     Col2 has the values 0 to 9, occuring 100 times each

rem     The Analyze ensures that Oracle has these stats

rem

drop table where_test;

create table where_test

unrecoverable

as

select

        rownum-1               col1,

        trunc((rownum-1)/100)  col2,

        rpad('x',200,'x')      padding

from

        sys.obj$

where rownum <= 1000

;

analyze table where_test compute statistics;

column low_value format a10

column high_value format a10

column column_name format a12

select 

        column_name,

        num_distinct, 

        low_value,

        high_value,

        density 

from    user_tab_columns 

where   column_name in ('COL1','COL2')

and     table_name = 'WHERE_TEST'

;

The Test Code

rem

rem     demo.sql

rem

rem     There is ONE row where

rem            col1 = 499

rem            col2 = 4

rem

connect jpl/jpl

alter session set sql_trace true;

select 

        where_test_p.where_test,

        col1,

        col2

from

        where_test

where

        col1 = 498 + where_test_p.where_test

and     col2 = 3 + where_test_p.where_test

;

connect jpl/jpl

alter session set sql_trace true;

select 

        where_test_p.where_test,

        col1,

        col2

from

        where_test

where

        col2 = 3 + where_test_p.where_test

and     col1 = 498 + where_test_p.where_test

;

The Conclusion:

As you note, the function does a 'select user from dual'. The first trace file showed 1,101 executions of this statement. The second trace file showed 1,002 executions of the statement.

So we can safely assume that

               col1 = 498 + where_test_p.where_test

        and     col2 = 3 + where_test_p.where_test

tested col2 1000 times, found 100 cases where its value was 4, and tested the corresponding value of col1 100 times.

Similarly

               col2 = 3 + where_test_p.where_test

        and     col1 = 498 + where_test_p.where_test

tested col1 1000 times, found 1 case where its value was 499, and test the corresponding value of col2 just once.

We conclude that Oracle 8.0, like Oracle 7, tests WHERE clause predicates from the bottom up when all other considerations have been assessed.