ORDERED_PREDICATES: puzzling enigma here

From: Spendius <spendius_at_muchomail.com>
Date: 11 May 2005 05:16:37 -0700
Message-ID: <1115813797.367847.110890@g47g2000cwa.googlegroups.com>

What's the purpose of this hint ?

And WHO can explain me why we don't get the ORA-01722 error at the end of the following in spite of the use of this hint (because saying first to Oracle "filter out everything that's not like '835%' and then all dates that are different from 31/10/2003" STILL returns zip non-null codes that are stored as varchar2, yet although we put "zip = 43933" the error no more appears !: why does the parser firstly reject "zip = 43933" to accept it afterwards ?!?, this is really something I can't understand):

(this is a copy/paste from a previous Wolfgang Breitling's post)

SQL> Create table p_order (
  2 SSN VARCHAR2(10),
  3 BIRTH_date DATE,
  4 ZIP VARCHAR2(10)); Table created.

SQL> insert into p_order values('8328486572', '31-OCT-03', '27722');
SQL> insert into p_order values('8397333761', '31-OCT-03', 'A1180');
SQL> insert into p_order values('8358002539', '31-OCT-03', '81225');
SQL> insert into p_order values('8353962080', '31-OCT-03', '43933');
SQL>
SQL> select * from p_order;

SSN        BIRTH_DAT ZIP

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

8328486572 31-OCT-03 27722
8397333761 31-OCT-03 A1180
8358002539 31-OCT-03 81225
8353962080 31-OCT-03 43933

SQL>
SQL> select * from p_order

  2       where ssn like '835%'
  3        and birth_date = '31-OCT-03';

SSN        BIRTH_DAT ZIP

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

8358002539 31-OCT-03 81225
8353962080 31-OCT-03 43933 SQL> ed
Wrote file afiedt.buf
1 select * from p_order

  2      where ssn like '835%'
  3       and birth_date = '31-OCT-03'
  4*      and zip = 43933
SQL> /
     and zip = 43933
         *

ERROR at line 4:
ORA-01722: invalid number

SQL> ed
Wrote file afiedt.buf
1 select /*+ ordered_predicates */ * from p_order

  2      where ssn like '835%'
  3       and birth_date = '31-OCT-03'
  4*      and zip = 43933

SQL> / SSN BIRTH_DAT ZIP
---------- --------- ----------

8353962080 31-OCT-03 43933 Thanks a lot for any enlightenment...
Spendius Received on Wed May 11 2005 - 07:16:37 CDT