Re: Re[JN]: to_number question
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 15 Jul 2004 23:22:33 +0200
----- Original Message -----
From: "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, July 15, 2004 8:27 PM
Subject: Re: Re[2]: to_number question
Quoting Jonathan Gennick <jonathan@xxxxxxxxxxx>:
> Astounding. It's also astounding I've never run into this
> issue before. It's not like I never write subqueries.
>
Hi all,
Small amendment, it is definitive not only topic of subqueries. Similar
"result" is possible to achieve playing with predicate order.
I use in my example ordered_predicate hint to demonstrate it, but I assume
that it is general accepted that the optimizer is free to reorder the
predicates resulting sometime in error sometimes in right answer.
In this case I see no "bug theory" explanation.
Regard
Jaromir D.B. Nemec
http://www.db-nemec.com
SQL> select * from x;
A B
---------- ----------
a 10
a 20
a 30
a 40
b 11
b 21
b 31
b 41b
8 rows selected.
SQL> select /*+ ordered_predicates */ a, to_number(b) from x where
to_number(b)
=10 and to_char(a) < 'b';
ERROR:
ORA-01722: invalid number
no rows selected
SQL> select /*+ ordered_predicates */ a, to_number(b) from x where
to_char(a) <
'b' and to_number(b) =10;
A TO_NUMBER(B)
---------- ------------
a 10
SQL> EXPLAIN PLAN set statement_id = 'N1' into nemecj.plan_table FOR
2 select /*+ ordered_predicates */ a, to_number(b) from x where
to_number(b)
=10 and to_char(a) < 'b';
Explained.
SQL> SELECT * FROM
table(DBMS_XPLAN.DISPLAY('nemecj.plan_table','N1','ALL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
----
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 5 (20)|
|* 1 | TABLE ACCESS FULL | X | 1 | 4 | 5 (20)|
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
----
1 - filter(TO_NUMBER("X"."B")=10 AND "X"."A"<'b')
12 rows selected.
SQL> EXPLAIN PLAN set statement_id = 'N2' into nemecj.plan_table FOR
2 select /*+ ordered_predicates */ a, to_number(b) from x where
to_char(a) <
'b' and to_number(b) =10;
Explained.
SQL> SELECT * FROM
table(DBMS_XPLAN.DISPLAY('nemecj.plan_table','N2','ALL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
----
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 5 (20)|
|* 1 | TABLE ACCESS FULL | X | 1 | 4 | 5 (20)|
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
----
1 - filter("X"."A"<'b' AND TO_NUMBER("X"."B")=10)
12 rows selected.
SQL> quit;
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
