RE: Filter operation

SYSDATE has to be evaluated at execution time not parse time hence the
KEY pstart/pstop plan and the FILTERs.
 
With the literals, the optimizer knows at parse time which partitions
are involved.
So, you say that you don't get the FILTER operations, but I expect you
also get non KEY pstart/pstop.

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of LS Cheng
Sent: 23 March 2010 14:45
To: Oracle Mailinglist
Subject: Filter operation


Hi all

I have this following query


    SELECT
      SUM(CA_MESURE) MESURE,
      F_PERIOD PERIOD
    FROM TB_MESURE_EO_GLA MF -- PARTITIONED BY F_PERIOD
    WHERE MF.F_PERIOD >= add_months(trunc(sysdate, 'MM'), -1)
      AND MF.F_PERIOD < trunc(sysdate, 'MM')
    GROUP BY F_PERIOD

shows this plan:

------------------------------------------------------------------------
------------------------
| Id  | Operation                  |  Name             | Rows  | Bytes |
Cost  | Pstart| Pstop |
------------------------------------------------------------------------
------------------------
|   0 | SELECT STATEMENT           |                   | 17641 |   189K|
17182 |       |       |
|   1 |  SORT GROUP BY             |                   | 17641 |   189K|
17182 |       |       |
|*  2 |   FILTER                   |                   |       |       |
|       |       |
|   3 |    PARTITION RANGE ITERATOR|                   |       |       |
|   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL      | TB_MESURE_EO_GLA  |  5449K|    57M|
12941 |   KEY |   KEY |
------------------------------------------------------------------------
------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   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'))

There is a filter operation which is making this query pretty slow, if I
change add_months and trunc(sysdate) to literals such as 

to_date('20100201 00:00:00', 'yyyymmdd hh24:mi:ss')
to_date('20100301 00:00:00', 'yyyymmdd hh24:mi:ss')

The filter operation no longer exists.

I wonder the need of a filter operation when functions are used? 

Any suggestions?

Thanks

--
LSC




**********************************************************************
 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 to ensure 
the integrity of its systems, for record keeping and regulatory purposes. 
Visit us at: www.maninvestments.com 
TG0908
**********************************************************************

Other related posts: