RE: Operator and condition precedence
Date: Tue, 8 Sep 2009 20:15:12 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF0154F692_at_WIN02.hotsos.com>
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_TABLE_OUTPUT
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
Hotsos Enterprises
Hotsos Symposium
March 7 - 11, 2010
Be there.
From: oracle-l-bounce_at_freelists.org
[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
false)
to_number(aa.col_2a) = bb.col_2b is never evaluated.
try this
select * from dual
where to_number('abc') > 0
and 'xyz' > '';
Mike
On Tue, Sep 8, 2009 at 1:49 PM, Rich Jesse <rjoralist_at_society.servebeer.com> wrote:
Hey all,
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.
Somewhat
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,
table_b bb
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:
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/condition s001.htm#sthref874
...tells me that the above would attempt to convert an AA.COL_2A to a
number
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?"
Thanks!
Rich
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 08 2009 - 20:15:12 CDT