| Order of execution in where clause [message #112158] |
Wed, 23 March 2005 06:38  |
Amit_jhansi80
Messages: 5
Registered: March 2005
Location: India
|
Junior Member |
|
|
Hi All,
I have one issue regarding the order of execution of where clause statements.
Let me explain –
create table x (segment1 varchar2(20))
Insert into x values (1)
insert into x values (2)
insert into x values (3)
insert into x values ('N/A')
SELECT * FROM x
Output:
SEGMENT1
========
1
2
3
N/A
Now when I run this query it is giving me right result:
SELECT *
FROM x
where to_number(segment1) =1
and segment1 <> 'N/A'
but when running following query, it is giving me error(ORA-01722 invalid number)
SELECT *
FROM x
where segment1 <> 'N/A'
and to_number(segment1) =1
Can someone explain this?
|
|
|
|
| Re: Order of execution in where clause [message #112175 is a reply to message #112158] |
Wed, 23 March 2005 08:32  |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
Oracle interprets WHERE predicates with the same presedence from right to left. The most restrictive clause should be the last one
in WHERE.
Oracle evaluates all predicates in the following order:
1. Predicates without user-defined functions, type methods, or subqueries are evaluated first, in the order specified in the WHERE clause.
2. Predicates with user-defined functions and type methods that have user-computed costs are evaluated next, in increasing order of their cost.
3. Predicates with user-defined functions and type methods without user-computed costs are evaluated next, in the order specified in the WHERE clause.
4. Predicates not specified in the WHERE clause (for example, predicates transitively generated by the optimizer) are evaluated next.
5. Predicates with subqueries are evaluated last, in the order specified in the WHERE clause.
SQL> select * from your_table;
NAME AGE
-------------------- ----------
Joe 24
Sam 29
Mark 37
Ian N/A
Max Old
Elapsed: 00:00:00.00
SQL> select * from your_table where age > 30 and age not in ('N/A','Old')
2 /
NAME AGE
-------------------- ----------
Mark 37
Elapsed: 00:00:00.00
SQL> select * from your_table where age not in ('N/A','Old') and age > 30;
ERROR:
ORA-01722: invalid number
no rows selected
Elapsed: 00:00:00.00
You can change the processing order to reverse using the hint:
SQL> select /*+ORDERED_PREDICATES */ * from your_table where age not in ('N/A','Old') and age > 30
2 /
NAME AGE
-------------------- ----------
Mark 37
Rgds.
|
|
|
|
|
|
|
|
| Re: Order of execution in where clause [message #112753 is a reply to message #112711] |
Tue, 29 March 2005 02:08 |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
In the case of indexes cost optimizer can change rowset
to which conditions are applied (I didn't collect statistics
here):
SQL> edit
Wrote file afiedt.buf
1* select /*+RULE*/ count(*) from your_table where id not in ('N/A','Old') and id < 5
SQL> /
select /*+RULE*/ count(*) from your_table where id not in ('N/A','Old') and id < 5
*
ERROR at line 1:
ORA-01722: invalid number
SQL> edit
Wrote file afiedt.buf
1* select /*+COST*/ count(*) from your_table where id not in ('N/A','Old') and id < 5
SQL> /
COUNT(*)
----------
8
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'A1' (NON-UNIQUE) (Cost=4 Card
=505 Bytes=2020)
Brackets also change the order of estimation (from left to right inside):
SQL> edit
Wrote file afiedt.buf
1 select /*+COST*/ count(*) from your_table
2* where id in ('N/A','Old') and (id > 100 or id != 'Old')
SQL> /
where id in ('N/A','Old') and (id > 100 or id != 'Old')
*
ERROR at line 2:
ORA-01722: invalid number
SQL> edit
Wrote file afiedt.buf
1 select /*+COST*/ count(*) from your_table
2* where id in ('N/A','Old') and (id != 'Old' or id > 100)
SQL> /
COUNT(*)
----------
100
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INLIST ITERATOR
3 2 INDEX (RANGE SCAN) OF 'A1' (NON-UNIQUE) (Cost=2 Card=2
Bytes=8)
SQL> edit
Wrote file afiedt.buf
1 select /*+RULE*/ count(*) from your_table
2* where id in ('N/A','Old') and (id != 'Old' or id > 100)
SQL> /
COUNT(*)
----------
100
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 SORT (AGGREGATE)
2 1 CONCATENATION
3 2 INDEX (RANGE SCAN) OF 'A1' (NON-UNIQUE)
4 2 INDEX (RANGE SCAN) OF 'A1' (NON-UNIQUE)
SQL> edit
Wrote file afiedt.buf
1 select /*+RULE*/ count(*) from your_table
2* where id in ('N/A','Old') and (id > 100 and id != 'Old')
SQL> /
where id in ('N/A','Old') and (id > 100 and id != 'Old')
*
ERROR at line 2:
ORA-01722: invalid number
Rgds.
|
|
|
|
| Re: Order of execution in where clause [message #112796 is a reply to message #112711] |
Tue, 29 March 2005 07:39 |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
More exactly:
1) no statistics, no indexes - default from right to left.
SQL> edit
Wrote file afiedt.buf
1 select count(1) from your_table
2* where id != 'N/A' and id < 4
SQL> /
where id != 'N/A' and id < 4
*
ERROR at line 2:
ORA-01722: invalid number
SQL> edit
Wrote file afiedt.buf
1 select count(1) from your_table
2* where id < 4 and id != 'N/A'
SQL> /
COUNT(1)
----------
6
2) statistics, no indexes
COST approach (default) - left to right:
SQL> edit
Wrote file afiedt.buf
1 select /*+COST*/ count(1) from your_table
2* where id != 'N/A' and id < 4
SQL> /
COUNT(1)
----------
6
SQL> edit
Wrote file afiedt.buf
1 select /*+COST*/ count(1) from your_table
2* where id < 4 and id != 'N/A'
SQL> /
where id < 4 and id != 'N/A'
*
ERROR at line 2:
ORA-01722: invalid number
RULE approach - right to left:
1 select /*+RULE*/ count(1) from your_table
2* where id != 'N/A' and id < 4
SQL> /
where id != 'N/A' and id < 4
*
ERROR at line 2:
ORA-01722: invalid number
3) statistics or no statistics, indexes:
can force to use indexes (prefer the conditions which
indexes can be used for):
SQL> select count(1) from your_table
2 where id != 99 and id = 'N/A'
3 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'A1' (NON-UNIQUE) (Cost=1 Card=1 B
ytes=4)
SQL> edit
Wrote file afiedt.buf
1 select /*+RULE*/ count(1) from your_table
2* where id != 99 and id = 'N/A'
SQL> /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'A1' (NON-UNIQUE)
Rgds.
[Updated on: Tue, 29 March 2005 07:49] Report message to a moderator |
|
|
|
| Re: Order of execution in where clause [message #112800 is a reply to message #112796] |
Tue, 29 March 2005 08:14 |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
Sorry, one small adding: this is true for 9i.
10g default work differs from above:
SQL> select count(1) from your_table2 where id != 'N/A' and id = 4
2 /
COUNT(1)
----------
2
SQL> edit
Wrote file afiedt.buf
1* select count(1) from your_table2 where id = 4 and id != 'N/A'
SQL> /
COUNT(1)
----------
2
Rgds.
|
|
|
|
| Re: Order of execution in where clause [message #113225 is a reply to message #112158] |
Thu, 31 March 2005 14:34 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I think it is unwise to rely on the order of query execution within the where clause when using CBO. The whole point of CBO is that it will find the "best" route, and the route it finds will change as your data and system changes, and as your oracle version changes. To rely on anything, even if it holds for the current oracle version, could and probably will lead to future problems.
The initial query should have been rewritting, to use a custom to_number function that traps the exception and returns something (such as null) when the conversion test fails.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
