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

Other related posts: