Trying to suppress predicate pushing.

This is just a learning exercise for me, not an actual problem. I know how
to restructure the query to get the desired results, but I am trying to
learn why my hints are not working.

CREATE TABLE pred_test(  charstr  VARCHAR2(10));
insert into pred_test values ('nnn123');
insert into pred_test values ('cccxyz');
COMMIT;

select * from
   (select to_number(substr(charstr,4,3)) numstr
      from pred_test where substr(charstr,1,3) = 'nnn')
where numstr = 123;

Error at line 2
ORA-01722: invalid number

explain plan for
select * from
   (select to_number(substr(charstr,4,3)) numstr
      from pred_test where substr(charstr,1,3) = 'nnn')
where numstr = 123;

set pagesize 0
set linesize 132
select * from table(dbms_xplan.display);

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

| Id  | Operation         | Name      | Rows  | Bytes | Cost  |

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

|   0 | SELECT STATEMENT  |           |     1 |     7 |     2 |

|*  1 |  TABLE ACCESS FULL| PRED_TEST |     1 |     7 |     2 |

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



Predicate Information (identified by operation id):

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



   1 - filter(TO_NUMBER(SUBSTR("CHARSTR",4,3))=123 AND

              SUBSTR("CHARSTR",1,3)='nnn')

Clearly, the problem is that the predicate "TO_NUMBER(SUBSTR("CHARSTR",4,3)"
is being pushed and evaluated first. What I want to happen is for the
inner query to run first, and in essence, pipe it's results to the outer
query.

I've tried hints, /*+ NO_PUSH_PRED(v) */ and /*+ ORDERED_PREDICATES */ with
no luck.

I CAN force the evaluation of the inner SELECT as follows,
 but my real question is why are these hints NOT working?

select  * from
   (select to_number(substr(charstr,4,3)) numstr
      from pred_test where substr(charstr,1,3) = 'nnn' and rownum > 0)
where numstr = 123;

thanks,
Mike

Other related posts: