Re: Trying to suppress predicate pushing.
- From: Ron Crisco <ron.crisco@xxxxxxxxxxxx>
- To: Michael Moore <michaeljmoore@xxxxxxxxx>
- Date: Wed, 21 Apr 2010 13:51:09 -0500
I verified that it worked (in 10.2.0.1) before I replied with the answer.
Ron
On Wed, Apr 21, 2010 at 1:49 PM, Michael Moore <michaeljmoore@xxxxxxxxx>wrote:
> 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:
