RE: Operator and condition precedence
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <michaeljmoore@xxxxxxxxx>, <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- 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_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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Michael Moore
Sent: Tuesday, September 08, 2009 4:36 PM
To: rjoralist@xxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
