RE: Why partition pruning is not happening?

  • From: "Abhay Suresh Kulkarni" <AbhKulkarni@xxxxxxxx>
  • To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 5 Jan 2007 19:33:48 +0530

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Nigel Thomas

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



 [Abhay]  Be sure the scalar subquery returns a single row, else you
will end up in ORA-01427. On multiple rows returning from this subquery,
you may use "IN" but then oracle may not consider partition purning. 







Other related posts: