Re[JN]: to_number question 2004-07-16 - By Khedr, Waleed
Actually the subquery gets converted to a sql that has two predicates
grouped by "AND " (similar to yours).
Using the hint "ordered predicates " makes it to work because Oracle does
a good job generating code that eliminates unnecessary predicates '
evaluation when the first predicate is false (since we have AND).
It 's not guaranteed that hints will always work or the database does
always a good job optimizing their machine code.
It 's recommended to write sql/code that describes the exact details of
the needed data and respects the domains of columns extracted or in the
predicates.
It 's easy to write code that works when you document your logic.
For example:
drop table test_domain;
create table test_domain ( c1 char, c2 char);
insert into test_domain values ( 'A ', '3 ');
insert into test_domain values ( 'B ', 'A ');
commit;
SQL > select *
2 from test_domain
3 where c1 =3D 'A '
4 and to_number(c2) > 2;
ERROR:
ORA-01722 (See ORA-01722.ora-code.com): invalid number
If there is a rule that says I care only for the rows that have c1 =3D =
'A '
and I know they have numeric values in c2 and I care nothing for the
other rows that have different values for c1, then this is easy to be
converted to sql:
SQL > select *
2 from test_domain
3 where c1 =3D 'A '
4 and to_number(decode(c1, 'A ',c2,0)) > 2;
C C
- -
A 3
Regards,
Waleed
-- --Original Message-- --
From: jaromir nemec [mailto:jaromir@(protected)]=20
Sent: Thursday, July 15, 2004 5:23 PM
To: oracle-l@(protected)
Subject: Re: Re[JN]: to_number question
-- -- Original Message -- --=20
From: "Wolfgang Breitling " <breitliw@(protected) >
To: <oracle-l@(protected) >
Sent: Thursday, July 15, 2004 8:27 PM
Subject: Re: Re[2]: to_number question
Quoting Jonathan Gennick <jonathan@(protected) >:
> 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)
=3D10 and to_char(a) < 'b ';
ERROR:
ORA-01722 (See ORA-01722.ora-code.com): invalid number
no rows selected
SQL > select /*+ ordered_predicates */ a, to_number(b) from x where
to_char(a) <
'b ' and to_number(b) =3D10;
A TO_NUMBER(B)
-- ---- -- -- ---- ----
a 10
SQL > EXPLAIN PLAN set statement_id =3D 'N1 ' into nemecj.plan_table FOR
2 select /*+ ordered_predicates */ a, to_number(b) from x where
to_number(b)
=3D10 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 ")=3D10 AND "X ". "A " < 'b ')
12 rows selected.
SQL > EXPLAIN PLAN set statement_id =3D '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) =3D10;
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 ")=3D10)
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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
