RE: Why partition pruning is not happening?

From: Abhay Suresh Kulkarni <AbhKulkarni_at_manh.com>
Date: Fri, 5 Jan 2007 19:33:48 +0530
Message-ID: <D23ECE9D466B5C48BB8EF2A546A60AC40174D136@ma-india10.asia.manh.com>

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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 = 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    

 [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.      

Rgds

Abhay.  

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

Received on Fri Jan 05 2007 - 08:03:48 CST