Re: Why partition pruning is not happening?

  • From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
  • To: exriscer@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 5 Jan 2007 04:51:24 -0800 (PST)

>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: