Re: Re[JN]: to_number question

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Thu, 15 Jul 2004 23:22:33 +0200
Message-ID: <04e401c46ab1$d8d32b50$3c02a8c0@JARAWIN>

Original Message ----- From: "Wolfgang Breitling" <breitliw_at_centrexcc.com> To: <oracle-l_at_freelists.org> Sent: Thursday, July 15, 2004 8:27 PM Subject: Re: Re[2]: to_number question

Quoting Jonathan Gennick <jonathan_at_gennick.com>:

> 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

| 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

| 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_at_freelists.org 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
-----------------------------------------------------------------

Received on Thu Jul 15 2004 - 16:21:10 CDT