----- 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 -----------------------------------------------------------------