Re: Filter operation
- From: LS Cheng <exriscer@xxxxxxxxx>
- To: Alex Fatkulin <afatkulin@xxxxxxxxx>
- Date: Tue, 23 Mar 2010 18:00:25 +0100
Hi Alex
dont understand very well why those two conditions why will always results
in FALSE?
I have two FILTER operations
2 - filter(ADD_MONTHS(TRUNC(SYSDATE@!,'fmmm'),-1)<TRUNC(SYSDATE@
!,'fmmm'))
4 - filter("MF"."F_PERIOD">=ADD_MONTHS(TRUNC(SYSDATE@!,'fmmm'),-1) AND
"MF"."F_PERIOD"<TRUNC(SYSDATE@!,'fmmm'))
For step 4 I understand that Filter is requiered but step 2....
Thanks!
--
LSC
On Tue, Mar 23, 2010 at 5:09 PM, Alex Fatkulin <afatkulin@xxxxxxxxx> wrote:
> 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@xxxxxxxxx> 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@xxxxxxxxxxxxxx> 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@xxxxxxxxx> wrote:
> >>>
> >>> Any suggestions?
> >>>
> >>> Thanks
> >>>
> >>> --
> >>> LSC
> >>>
> >>>
> >>
> >
> >
>
>
>
> --
> Alex Fatkulin,
> http://afatkulin.blogspot.com
> http://www.linkedin.com/in/alexfatkulin
>
Other related posts:
