RE: Operator and condition precedence
Date: Tue, 8 Sep 2009 20:15:12 -0500
I presume that the database is using CPU COST based optimization. The predicates are not evaluated in reverse order. The CPU COST model will rearrange the predicates into the order it thinks is easiest to evaluate.
The best way to see this is to look at the explain plan and check the predicate list below the plan to see what predicates are being applied and in what order.
Most likely the optimizer has shifted the TO_NUMBER after the simple predicates, so what Mike says is true, just not exactly for the reason Mike states (Mike, RULE based optimization does exactly what you say, not COST based).
Using you example Mike, notice that the predicate get reversed in the explain plan. The CPU COST model has switched them, it's basic logic is "Hey it easy to the do that predicate without the function call so I'll do it last, and hopefully not have to do it at least some of the time."
SQL> explain plan for
2 select * from dual
3 where to_number('abc') > 0
4 and 'xyz' > '';
SQL> SQL> select * from table(dbms_xplan.display);
Plan hash value: 3752461848
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter('xyz'>'' AND TO_NUMBER('abc')>0)
Ric Van Dyke
March 7 - 11, 2010
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael Moore Sent: Tuesday, September 08, 2009 4:36 PM To: rjoralist_at_society.servebeer.com
Cc: Oracle L
Subject: Re: Operator and condition precedence
I think this is what is happening.
Predicates are evaluated in reverse order.
Since and aa.col_2a > ' ' is always false (comparison to NULL is always
to_number(aa.col_2a) = bb.col_2b is never evaluated.
select * from dual
where to_number('abc') > 0
and 'xyz' > '';
On Tue, Sep 8, 2009 at 1:49 PM, Rich Jesse <rjoralist_at_society.servebeer.com> wrote:
A coworker asks me about casting an NCHAR column to NUMBER in a query on
10.1.0.5.0 (AIX), so I of course offer up the syntax of TO_NUMBER.
knowing the data, I warn that conversions on columns of all spaces will fail. But for some reason, this works:
select aa.stuff, bb.stuff
from table_a aa,
where aa.col_1a = bb.col_1b
and to_number(aa.col_2a) = bb.col_2b
and aa.col_3a = bb.col_3b and aa.col_4b = bb.col_4b and aa.col_6a in ('YY','ZZ') and aa.col_2a > ' ' and aa.col_5a <> bb.col_5b;
Looking at the docs:
...tells me that the above would attempt to convert an AA.COL_2A to a
before filtering it out from the AA.COL_2A > ' ' part, but it obviously doesn't. And there are known values of AA.COL_2A where it's all spaces.
The above statement works. My question is "How?"