Re: Why partition pruning is not happening?

  • From: "LS Cheng" <exriscer@xxxxxxxxx>
  • To: "Nigel Thomas" <nigel_cl_thomas@xxxxxxxxx>
  • Date: Fri, 5 Jan 2007 20:44:13 +0100


PRM_FEDIA does not return only one row when id_mes is 200611, it returns 30.

The table has a structure more or less as follows

fe_dia date
id_mes number

with id_mes 200611 we have this set of data

fe_dia       id_mes
----------   -----------
01/11/06     200611
02/11/06     200611
03/11/06     200611
and so on .............
20/11/06     200611

May be CBO is favouring PRM_SITE because since it uses PK only onw rows is
returned? May be I should rewrite the query so instead of

a12.id_mes = 200611


a12.fe_dia between to_date('20061101 000000', 'yyyymmdd hh24miss') and
235959', 'yyyymmdd hh24miss')
This query is generated by ad hoc tools (Microstrategy), I would need to
have a look how can the tool changes the query.

Thanks all


On 1/5/07, Nigel Thomas <nigel_cl_thomas@xxxxxxxxx> wrote:

 >I have a DWH query which IMHO should do partition prunning but it does
not. The query is as follows...

With your query, the CBO can't be sure that only one value of FE_DIA will
be returned (BTW, perhaps it could tell that if FE_DIA's the one and only PK
column for PRM_FEDIA), Try replacing the join to PRM_FEDIA with a scalar
subquery - then the optimiser should be able to tell that only one value for
FE_DIA can be referenced.

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_site a13,
         prm_tibusqbrs a14,
         prh_bqbusq a11     -- PARTITIONED BY FE_DIA
   WHERE a11.fe_dia = (SELECT fe_dia FROM prm_fedia WHERE id_mes = 200611)
     AND a11.id_site = a13.id_site
     AND a11.id_tp_busq = a14.id_tp_busq
     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

Regards Nigel

Other related posts: