Re: Trying to suppress predicate pushing.
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: Ron Crisco <ron.crisco@xxxxxxxxxxxx>
- Date: Wed, 21 Apr 2010 11:49:42 -0700
Hi Ron,
I understand about using the table alias in the hint, but even that does not
work in this case.
Thanks,
Mike
On Wed, Apr 21, 2010 at 11:44 AM, Ron Crisco <ron.crisco@xxxxxxxxxxxx>wrote:
> 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:
