RE: Filter operation

From: Brooks, Dominic (London)(c) <"Brooks,>
Date: Tue, 23 Mar 2010 16:12:57 -0000
Message-ID: <882B6289F381484AA6FA6B7FD2E4D5D1062AD14B_at_mildnpexmb01.maninvestments.ad.man.com>

That just seems to be a typo.
LSC's original post has the range predicates the right way round.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Alex Fatkulin Sent: 23 March 2010 16:09
To: exriscer_at_gmail.com
Cc: nigel.cl.thomas_at_googlemail.com; Oracle Mailinglist Subject: Re: Filter operation

Can you give us some data as to which extent that filter condition slows the query down?

The reason Oracle puts a filter there is because you may as well write a query like

MF.F_PERIOD < add_months(trunc(sysdate, 'MM'), -1)

      AND MF.F_PERIOD >= trunc(sysdate, 'MM')

which will always result in "false" hence Oracle would be able to short-circuit the execution without doing actual filtering on the fetched rows.

If you try your select with literals "reversed" you should see the filter operation appear back and Oracle using it to short-circuit the execution.

The bottom line is that that expression should merely be evaluated as a "short-circuit" opportunity and should not result in any measurable slowdown. If you see negative performance implications you're most probably looking at a bug.

On Tue, Mar 23, 2010 at 11:45 AM, LS Cheng <exriscer_at_gmail.com> wrote:
> Hi Nigel
>
> I changed as you suggested but it still doing Filter operation?
>
> The only way I see is use dynamic SQL
>
> Btw this is 9.2.0.8
>
>
> Thanks
>
>
>
> On Tue, Mar 23, 2010 at 4:12 PM, Nigel Thomas
> <nigel.cl.thomas_at_googlemail.com> wrote:
>>
>> Is it evaluating SYSDATE for every row (because the result of SYSDATE
>> changes over the course of the execution)? Might be better to put the
>> range bounds into variables - eg
>>
>> BEGIN
>>    :end_date := add_months(trunc(sysdate, 'MM'), -1);
>>  :start_date := trunc(sysdate, 'MM'); END;
>>
>> and then change your predicates:
>>
>>     WHERE MF.F_PERIOD >= :end_date
>>       AND MF.F_PERIOD < :start_date
>>
>> Regards Nigel
>>
>> On 23 March 2010 14:44, LS Cheng <exriscer_at_gmail.com> wrote:
>>>
>>> Any suggestions?
>>>
>>> Thanks
>>>
>>> --
>>> LSC
>>>
>>>
>>
>
>

--
Alex Fatkulin,
http://afatkulin.blogspot.com
http://www.linkedin.com/in/alexfatkulin
--
http://www.freelists.org/webpage/oracle-l



**********************************************************************
 Please consider the environment before printing this email or its attachments.
The contents of this email are for the named addressees only.  It contains information which may be confidential and privileged.  If you are not the intended recipient, please notify the sender immediately, destroy this email and any attachments and do not otherwise disclose or use them. Email transmission is not a secure method of communication and Man Investments cannot accept responsibility for the completeness or accuracy of this email or any attachments. Whilst Man Investments makes every effort to keep its network free from viruses, it does not accept responsibility for any computer virus which might be transferred by way of this email or any attachments. This email does not constitute a request, offer, recommendation or solicitation of any kind to buy, subscribe, sell or redeem any investment instruments or to perform other such transactions of any kind. Man Investments reserves the right to monitor, record and retain all electronic communications through its network t
 o ensure the integrity of its systems, for record keeping and regulatory purposes. 
Visit us at: www.maninvestments.com 
TG0908
**********************************************************************

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 23 2010 - 11:12:57 CDT