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

Other related posts: