Re: Why partition pruning is not happening?

Hi

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

use

a12.fe_dia between to_date('20061101 000000', 'yyyymmdd hh24miss') and to_date('20061130
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

--
LSC



On 1/5/07, Nigel Thomas <nigel_cl_thomas_at_yahoo.com> 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

>


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