• Questions
  • Order of predicate evaluation in queries

Breadcrumb

Announcement

Forty years

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, Nick.

Asked: May 24, 2001 - 2:03 pm UTC

Answered by: Tom Kyte - Last updated: November 17, 2004 - 2:13 pm UTC

Category: Database - Version: 8.1.5

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: On Connecting, Pivoting, and Learning New Things

You Asked

Hi Tom,
I was browsing the answer you've supplied to another friend about the order in which Oracle evaluates the predicates in the WHERE section of a SQL statement (by the way the example you provide in your answer is really frightening). I was wondering, whether proper parenthesising would do any good in suggesting an order of predicate evaluation.
That is, will

select * from emp, dept
where (emp.dept_no = dept.dept_no) and
((dept.dept_no = 1))

AND

select * from emp, dept
where emp.dept_no = dept.dept_no and
dept.dept_no = 1

make any difference at all (for the optimizer) or not? I mean regardless of the existence of various indexes and statistics
that will favour either order of predicate evaluation.
Thank's a lot and keep on the good work.


and we said...

No, the order of evaluation of a predicate in SQL is purposely "indeterminate".

You should not, cannot rely on a specific order or evaluation. You cannot even rely on short-circut evaluation. The optimizer will evaluate the predicate in the order it feels is best -- that is its job.

SQL is a wholly non-procedural language, you are trying to attribute procedural language to it. You should not have any need to have a predicate evaluated in some order.


If anyone can come up with a good reason for needing a predicate evaluated in some specific order -- put it in the comments and I'll address them as they come in.



and you rated our response

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Nick - Order of predicate evaluation

May 25, 2001 - 1:51 am UTC

Reviewer: A reader

Thanks a lot Tom. It was very clarifying.

What about this one?

May 09, 2003 - 4:37 pm UTC

Reviewer: Basil

I don't understand why Oracle would bother scanning here, when the bind variable, a constant, can be tested quickly.
Is this a case?

SQL> create table t as select object_name from all_objects;

Table created.

SQL> insert into t select * from t;

24754 rows created.

SQL> insert into t select * from t;

49508 rows created.

SQL> insert into t select * from t;

99016 rows created.

SQL> insert into t select * from t;

198032 rows created.

SQL> insert into t select * from t;

396064 rows created.

SQL> commit;

Commit complete.

SQL> create index t_idx on t(object_name);

Index created.

SQL> analyze index t_idx compute statistics;

Index analyzed.

SQL> variable p_name varchar2(50);
SQL> exec :p_name := NULL;

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly explain statistics
SQL>
SQL> select object_name from t where :p_name is null or object_name like :p_name
;

792128 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'T'


 

Tom Kyte

Followup  

May 09, 2003 - 5:15 pm UTC

you are using the brain dead RBO.

analyze the *table*, use the CBO.

Doh. Bad example

May 09, 2003 - 4:41 pm UTC

Reviewer: Basil.

Will get another one together

Why I need predicate evaluated in a particular order

May 09, 2003 - 5:42 pm UTC

Reviewer: Robert from Memphis, USA

Tom,

This is something I ran into about a year ago.

select *
from t1
where no_index_column = 'xyz'
and col2 = complicated_function(col3)
/

In the above case, I would want Oracle to do the inexpensive comparison on 'no_index_column' before testing col2 vs. the expensive function result.

Thanks,

Robert.


Tom Kyte

Followup  

May 09, 2003 - 7:03 pm UTC

select *
from t1
where decode( no_index_column, 'xyz', complicated_function(col3), null ) = col2;




ORDERED_PREDICATES

May 11, 2003 - 6:47 am UTC

Reviewer: Adrian from UK

Tom

I take it you don't like the ORDERED_PREDICATES hint then ?

Regards
Adrian

Tom Kyte

Followup  

May 11, 2003 - 10:01 am UTC

nope.

hints are hints, relying on them to get a specific execution path is fragile at best.

when you can easily do this in a 100% assured fashion using DECODE or CASE, why you would bother with a hint.....

May 12, 2003 - 10:10 am UTC

Reviewer: Robert from Memphis, USA


June 02, 2004 - 1:59 pm UTC

Reviewer: A reader

Similar to one of the reviewers, I want a certain order of predicate evaluation because some predicates are much more expensive to evaluate than others...

I have a query like

select * from gnarly_view
where complicated_function = 'FALSE'
and some_date > to_date('05/28/2004','MM/DD/YYYY')

I want the date predicate to execute first and tackle the complicated function only if it the date filter passes. How can I do this using the decode?

Another thing...

select *
from t1
where decode( no_index_column, 'xyz', complicated_function(col3), null ) =
col2;

In this case, suppose the 'no_index_column' were actually indexed, wrapping it in the decode like this would make it not use the index, right? i.e.

where indexed_column='xyz' will use the index but
where decode(indexed_column,'xyz',1,null)=1 will not use the index. Any way to avoid this?

Thanks

Tom Kyte

Followup  

June 02, 2004 - 2:20 pm UTC

well, if indexed_column = 'xyz' is in there
AND
the index should be used
THEN
the predicate "indexed_column = 'xyz' " is going to be evaluated first.

the index range scan would ensure that.


If not, if they are just filters to be applied, then DECODE() would be an appropriate technique.


but you can always

where indexed_column = 'xyz'
and decode( indexed_column, 'xyz', f(x), 0 ) = 1;



June 02, 2004 - 2:30 pm UTC

Reviewer: A reader

Ah, the

where indexed_column = 'xyz'
and decode( indexed_column, 'xyz', f(x), 0 ) = 1;

is a good trick. If the index helps, it will be used and also have the side-effect of evaluating my desired predicate first. If it is not useful (not selective enough), the decode() trick still ensures that my expensive function is called only for non-'xyz' rows. Thanks!

Could you please answer the other part of my question...

select * from gnarly_view
where complicated_function = 'FALSE'
and some_date > to_date('05/28/2004','MM/DD/YYYY')

I want the date predicate to execute first and tackle the complicated function only if it the date filter passes. How can I do this using the decode?

Tom Kyte

Followup  

June 02, 2004 - 2:45 pm UTC

you could use SIGN


decode( sign( some_date-to_date(..) ), 1, f(x) )


sign returns +1 when input is >0
returns 0 when input is 0
returns -1 when input is <0


you could also use CASE

where
case when some_date > to_date( ... )
then f(x)
end = 1




Literal IN columns

November 17, 2004 - 10:11 am UTC

Reviewer: A reader

How does the CBO evaluate the following

'foo' in (col1,col2)

vs

(col1='foo' or col2='foo')

Are they 100% equivalent, if not, how would they differ?

Thanks



Tom Kyte

Followup  

November 17, 2004 - 2:01 pm UTC

ops$tkyte@ORA9IR2> delete from plan_table;
 
2 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for select * from dual where dummy in ( 'X', 'Y' );
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  TABLE ACCESS FULL   | DUAL        |       |       |       |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DUAL"."DUMMY"='X' OR "DUAL"."DUMMY"='Y')
 
Note: rule based optimization
 
14 rows selected.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;
 
2 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for select * from dual where dummy = 'X' or dummy = 'Y';
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  TABLE ACCESS FULL   | DUAL        |       |       |       |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DUAL"."DUMMY"='X' OR "DUAL"."DUMMY"='Y')
 
Note: rule based optimization
 
14 rows selected.
 
 

November 17, 2004 - 2:09 pm UTC

Reviewer: A reader

I was asking about the difference between

'foo' in (i,j)
vs
(i='foo' or j='foo')

not between

i in ('foo','bar')
vs
(i='foo' or i='bar')

But the answer is the same. CBO does seem to treat the predicates as 100% equivalent.

Thanks

Tom Kyte

Followup  

November 17, 2004 - 2:13 pm UTC


ops$tkyte@ORA9IR2> create table t ( x varchar2(50), y varchar2(50) );
 
Table created.
 
ops$tkyte@ORA9IR2> create index t1idx on t(x);
 
Index created.
 
ops$tkyte@ORA9IR2> create index t2idx on t(y);
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;
 
0 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for select * from t where 'x' in ( x,y );
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
----------------------------------------------------------------------------
| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |       |       |
|   1 |  CONCATENATION               |             |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| T           |       |       |       |
|*  3 |    INDEX RANGE SCAN          | T2IDX       |       |       |       |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T           |       |       |       |
|*  5 |    INDEX RANGE SCAN          | T1IDX       |       |       |       |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T"."Y"='x')
   4 - filter(LNNVL("T"."Y"='x'))
   5 - access("T"."X"='x')
 
Note: rule based optimization
 
20 rows selected.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;
 
6 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for select * from t where 'x' = x or 'x' = y;
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
----------------------------------------------------------------------------
| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |       |       |
|   1 |  CONCATENATION               |             |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| T           |       |       |       |
|*  3 |    INDEX RANGE SCAN          | T2IDX       |       |       |       |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T           |       |       |       |
|*  5 |    INDEX RANGE SCAN          | T1IDX       |       |       |       |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T"."Y"='x')
   4 - filter(LNNVL("T"."Y"='x'))
   5 - access("T"."X"='x')
 
Note: rule based optimization
 
20 rows selected.