Re: Trying to suppress predicate pushing.
- From: Ron Crisco <ron.crisco@xxxxxxxxxxxx>
- To: Michael Moore <michaeljmoore@xxxxxxxxx>
- Date: Wed, 21 Apr 2010 14:09:30 -0500
Here's what I see:
SQL> l
1 select /*+ NO_PUSH_PRED(v) */ * from
2 (select to_number(substr(charstr,4,3)) numstr
3 from pred_test where substr(charstr,1,3) = 'nnn') v
4* where numstr=123
SQL> /
NUMSTR
---------------
123
SQL> select version from v$instance;
VERSION
-----------------
10.2.0.1.0
SQL> select * from pred_test;
CHARSTR
----------
nnn123
cccxyz
SQL>
On Wed, Apr 21, 2010 at 1:54 PM, Michael Moore <michaeljmoore@xxxxxxxxx>wrote:
> That's weird because this is what I get.
>
> 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
> *
> Error at line 0
> ORA-01722: invalid number
>
> We are also running 10.2.
>
>
>
>
>
> On Wed, Apr 21, 2010 at 11:51 AM, Ron Crisco <ron.crisco@xxxxxxxxxxxx>wrote:
>
>> 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:
