Why partition pruning is not happening?

From: LS Cheng <exriscer_at_gmail.com>
Date: Fri, 5 Jan 2007 13:19:01 +0100
Message-ID: <6e9345580701050419m620e0b48qc3129d50a28c6cd8@mail.gmail.com>

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

| 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

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 05 2007 - 06:19:01 CST