Re: Trying to suppress predicate pushing.

You are 99.99% there. Your hint must reference a specific view or it will be
ignored. Use this:

 select /*+ NO_PUSH_PRED(v) */ * from
  (select to_number(substr(charstr,4,3)) numstr
        from pred_test where substr(charstr,1,3) = 'nnn') v
  where numstr=123


Ron Crisco

On Wed, Apr 21, 2010 at 1:19 PM, Michael Moore <michaeljmoore@xxxxxxxxx>wrote:

> 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: