Re: Filter operation

  • From: Tim Gorman <tim@xxxxxxxxx>
  • To: exriscer@xxxxxxxxx
  • Date: Tue, 23 Mar 2010 10:35:48 -0600

LSC,

Go ahead and use dynamic SQL in order to show literal data values to the optimizer at parse time in order to facilitate partition pruning.  For data warehouse workloads, the proliferation of versions of the SQL statement due to usage of literals should not be so great as to overwhelm the Shared Pool;  however, this might not be the case in an OLTP workload, so be cautious.

Hope this helps...
Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal     => P.O. Box 630791, Highlands Ranch CO  80163-0791
website    => http://www.EvDBT.com/
email      => Tim@xxxxxxxxx
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...


LS Cheng 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




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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2010