| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re[JN]: to_number question
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;
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_at_db-nemec.com]=20
Sent: Thursday, July 15, 2004 5:23 PM
To: oracle-l_at_freelists.org
Subject: Re: Re[JN]: 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
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: 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
-- 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_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 Fri Jul 16 2004 - 00:19:31 CDT
 |
 |