Re: to_number question

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Sat, 17 Jul 2004 15:58:49 +0300
Message-ID: <02c601c46bfd$ce1cac20$0a879fd9@porgand>

I did some testing (on 9.2.0.4) and as expected, the predicate evaluation order will depend on underlying table stats as well. For example running the problematic query using CBO with default statistics (no stats gathered, forced CBO with FIRST_ROWS hint), that way the query worked, but when I analyzed the table, predicate orders were different and query failed.

I wonder if there is any way other than 10053 and 10060 trace (the latter seems to be used by dbms_xplan.display) to get information about Oracle's decisions on predicate ordering (e.g. which stats were relevant etc..)?

An example of my test below..

Tanel.

SQL> drop table subtest;

Table dropped.

SQL>
SQL> create table subtest (flag varchar2(10), num varchar2(20));

Table created.

SQL>
SQL> insert into subtest values ('A', '12345');

1 row created.

SQL> insert into subtest values ('C', '54321');

1 row created.

SQL> insert into subtest values ('F', 'XXXXX');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> SELECT /*+ FIRST_ROWS */ *
2 FROM ( SELECT flag, TO_NUMBER(num) num

  3         FROM subtest
  4         WHERE flag IN ('A', 'C') )

5 WHERE num > 0;

FLAG NUM
---------- ----------

A               12345
C               54321

2 rows selected.

SQL>
SQL> @x
SQL> set termout off

PLAN_TABLE_OUTPUT

|   0 | SELECT STATEMENT     |             |     1 |    19 |     3  (34)|
|*  1 |  TABLE ACCESS FULL   | SUBTEST     |     1 |    19 |     3  (34)|


-------------------------------------------------------------------------

Predicate Information (identified by operation id):

1 - filter(("SUBTEST"."FLAG"='A' OR "SUBTEST"."FLAG"='C') AND

TO_NUMBER("SUBTEST"."NUM")>0) SQL> PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('ADMIN', 'SUBTEST'); <<<<!!!!! ---- Here I analyzed the table and predicate ordering changed.

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT /*+ FIRST_ROWS */ *
2 FROM ( SELECT flag, TO_NUMBER(num) num

  3         FROM subtest
  4         WHERE flag IN ('A', 'C') )

5 WHERE num > 0;
ERROR:
ORA-01722: invalid number

no rows selected

SQL>
SQL> @x
SQL> set termout off

PLAN_TABLE_OUTPUT

|   0 | SELECT STATEMENT     |             |     1 |     8 |     3  (34)|
|*  1 |  TABLE ACCESS FULL   | SUBTEST     |     1 |     8 |     3  (34)|


-------------------------------------------------------------------------

Predicate Information (identified by operation id):

1 - filter(TO_NUMBER("SUBTEST"."NUM")>0 AND ("SUBTEST"."FLAG"='A' OR

"SUBTEST"."FLAG"='C'))
SQL>

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 Sat Jul 17 2004 - 07:55:27 CDT