Filter operation
- From: LS Cheng <exriscer@xxxxxxxxx>
- To: Oracle Mailinglist <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 23 Mar 2010 15:44:48 +0100
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:
