Re: Operator and condition precedence
- From: Tanel Poder <tanel@xxxxxxxxxx>
- To: Martin Berger <martin.a.berger@xxxxxxxxx>
- Date: Thu, 10 Sep 2009 03:31:53 +0800
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@xxxxxxxxx>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
>
>
>
>
Other related posts:
