Re: Trying to suppress predicate pushing.
- From: Ron Crisco <ron.crisco@xxxxxxxxxxxx>
- To: michaeljmoore@xxxxxxxxx
- Date: Wed, 21 Apr 2010 13:44:01 -0500
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:
