JL Computer Consultancy

WHERE clause execution with NO indexes - Oracle 8.0

Updated:  Mar 2005

 

Created: May 1999


Addendum: 13th 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.


Back to Main Index of Topics