Why partition pruning is not happening?
- From: "LS Cheng" <exriscer@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Jan 2007 13:19:01 +0100
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
Other related posts:
