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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: