Nick - Order of predicate evaluation
A reader, May 25, 2001 - 1:51 am UTC
Thanks a lot Tom. It was very clarifying.
What about this one?
Basil, May 09, 2003 - 4:37 pm UTC
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'
May 09, 2003 - 5:15 pm UTC
you are using the brain dead RBO.
analyze the *table*, use the CBO.
Doh. Bad example
Basil., May 09, 2003 - 4:41 pm UTC
Will get another one together
Why I need predicate evaluated in a particular order
Robert, May 09, 2003 - 5:42 pm UTC
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.
May 09, 2003 - 7:03 pm UTC
select *
from t1
where decode( no_index_column, 'xyz', complicated_function(col3), null ) = col2;
ORDERED_PREDICATES
Adrian, May 11, 2003 - 6:47 am UTC
Tom
I take it you don't like the ORDERED_PREDICATES hint then ?
Regards
Adrian
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.....
Robert, May 12, 2003 - 10:10 am UTC
A reader, June 02, 2004 - 1:59 pm UTC
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
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;
A reader, June 02, 2004 - 2:30 pm UTC
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?
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
A reader, November 17, 2004 - 10:11 am UTC
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
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.
A reader, November 17, 2004 - 2:09 pm UTC
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
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.