Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Why partition pruning is not happening?

Re: Why partition pruning is not happening?

From: Nigel Thomas <nigel_cl_thomas_at_yahoo.com>
Date: Fri, 5 Jan 2007 04:51:24 -0800 (PST)
Message-ID: <20070105125124.2877.qmail@web58714.mail.re1.yahoo.com>


>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

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jan 05 2007 - 06:51:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US