Re: Operator and condition precedence
Date: Thu, 10 Sep 2009 03:31:53 +0800
Message-ID: <4602f23c0909091231t144f1211n84068a6d05a09963_at_mail.gmail.com>
Oh, it looks like IS NULL (or = '') stuff is checked differently than when comparing to a real value.
Execution plan's different (10.2.0.4):
*First checking for NULL (as empty string '' is NULL in oracle):
*select * from dual where dummy = '' and to_number(dummy) > 0
| Id | Operation | Name | E-Rows |
|* 1 | FILTER | | | |* 2 | TABLE ACCESS FULL| DUAL | 1 | --------------------------------------------
Predicate Information (identified by operation id):
- 1 - filter(NULL IS NOT NULL)
- 2 - filter(TO_NUMBER("DUMMY")>0)
*In above case we never get to predicate 2 - the to_number() > 0 check as
the parent rowsource filter always evaluates to false.
When comparing to a real value, the query fails:
*
SQL> select * from dual where dummy = 'blah' and to_number(dummy) > 0;
select * from dual where dummy = 'blah' and to_number(dummy) > 0
*
ERROR at line 1:
ORA-01722: invalid number
| Id | Operation | Name | E-Rows |
|* 1 | TABLE ACCESS FULL| DUAL | 1 |
Predicate Information (identified by operation id):
1 - filter((*TO_NUMBER("DUMMY")>0* AND "DUMMY"='blah'))
*So in above case the filter predicate checks are done in "wrong order",
first the TO_NUMBER check for some reason.
I can work around this with ordered predicates hint and the query works again:
*SQL> select /*+ ordered_predicates */ * from dual where dummy = 'blah' and
to_number(dummy) > 0;
no rows selected
| Id | Operation | Name | E-Rows |
|* 1 | TABLE ACCESS FULL| DUAL | 1 |
Predicate Information (identified by operation id):
1 - filter(("DUMMY"='blah' AND TO_NUMBER("DUMMY")>0))
*
Predicate order above is different from previous case...
*--
Tanel Poder
http://blog.tanelpoder.com
*
*
On Thu, Sep 10, 2009 at 3:17 AM, Martin Berger <martin.a.berger_at_gmail.com>wrote:
> thank you Tanel for this additiional info: '' <-> NULL (and it's shourcut)
> so one more test:
>
> select * from dual where 'xyz'>' ' and to_number('abc') > 0
> *
> ERROR at line 1:
> ORA-01722: invalid number
>
> select * from dual where to_number('abc') > 0 and 'xyz'>' '
> *
> ERROR at line 1:
> ORA-01722: invalid number
>
> select * from dual where dummy > ' ' and to_number(dummy) > 0
> *
> ERROR at line 1:
> ORA-01722: invalid number
>
> select * from dual where to_number(dummy) > 0 and dummy>' '
> *
> ERROR at line 1:
> ORA-01722: invalid number
>
> So with '<space>' instead of '' (equals NULL), all these 4 statements
> fails, without any difference in order of if it's based on a constant or
> column.
>
> funny to play :-) (no, I will not check 10053 trace, it's to late for me
> today)
> Martin
>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Sep 09 2009 - 14:31:53 CDT