A recent 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.