Partitioned on FE_DIA with an additional filter on ID_MES. When the CBO creates the plan, how should he knows that ID_MES is always a logical subset within FE_DIA? There is not transitive predicate between FE_DIA and ID_MES and ID_MES is not organized as a ranged sub-partition of FE_DIA. As it is : a11.fe_dia = a12.fe_dia is a join condition that could be satisfied by all partitions and a12.id_mes = 200611 Is a filter that could be true in any partitions. Bernard Polarski From: LS Cheng [mailto:exriscer@xxxxxxxxx] Sent: vrijdag 5 januari 2007 13:19 To: ORACLE-L Subject: Why partition pruning is not happening? Hi I have a DWH query which IMHO should do partition prunning but it does not. The query is as follows: SELECT a11.id_tp_busq id_tp_busq, a14.dt_busqueda dt_busqueda, a11.id_site id_site, a13.de_site de_site, SUM (a11.nt_busqueda) wjxbfs1 FROM prm_fedia a12, prm_site a13, prm_tibusqbrs a14, prh_bqbusq a11 -- PARTITIONED BY FE_DIA WHERE a11.fe_dia = a12.fe_dia AND a11.id_site = a13.id_site AND a11.id_tp_busq = a14.id_tp_busq AND a12.id_mes = 200611 AND a13.id_site = 1 AND a11.tx_termino LIKE 'Without usage%' GROUP BY a11.id_tp_busq, a14.dt_busqueda, a11.id_site, a13.de_site ------------------------------------------------------------------------ ------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | ------------------------------------------------------------------------ ------------------------------------ | 0 | SELECT STATEMENT | | 2 | 154 | 113 | | | | 1 | SORT GROUP BY | | 2 | 154 | 113 | | | | 2 | NESTED LOOPS | | 2 | 154 | 110 | | | | 3 | NESTED LOOPS | | 2 | 130 | 108 | | | | 4 | NESTED LOOPS | | 2 | 104 | 106 | | | | 5 | TABLE ACCESS BY INDEX ROWID | PRM_SITE | 1 | 17 | 1 | | | |* 6 | INDEX UNIQUE SCAN | PRM_SITE_PK | 221 | | | | | | 7 | PARTITION RANGE ALL | | | | | 1 | 25 | |* 8 | TABLE ACCESS BY LOCAL INDEX ROWID| PRH_BQBUSQ | 2 | 70 | 105 | 1 | 25 | |* 9 | INDEX RANGE SCAN | PRH_BQBUSQ_LN3 | 100 | | 50 | 1 | 25 | |* 10 | TABLE ACCESS BY INDEX ROWID | PRM_FEDIA | 1 | 13 | 1 | | | |* 11 | INDEX UNIQUE SCAN | PRM_FEDIA_PK | 85 | | | | | | 12 | TABLE ACCESS BY INDEX ROWID | PRM_TIBUSQBRS | 1 | 12 | 1 | | | |* 13 | INDEX UNIQUE SCAN | PRM_TIBUSQBRS_PK | 1 | | | | | ------------------------------------------------------------------------ ------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 6 - access("A13"."ID_SITE"=1) 8 - filter("A11"."ID_SITE"=1) 9 - access("A11"."TX_TERMINO" LIKE 'Without usage%') filter("A11"."TX_TERMINO" LIKE 'Without usage%') 10 - filter("A12"."ID_MES"=200611) 11 - access("A11"."FE_DIA"="A12"."FE_DIA") 13 - access("A11"."ID_TP_BUSQ"="A14"."ID_TP_BUSQ") I was expecting the condition a11.fe_dia = a12.fe_dia eliminates partitions and only reads 2006 November Partition. Any clues why it is not happening...? This is 9.2.0.4 running on HPUX Cheers -- LSC