RE: Operator and condition precedence

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
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-l
Received on Tue Sep 08 2009 - 20:15:12 CDT

Original text of this message