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: LS Cheng <exriscer_at_gmail.com>
Date: Fri, 5 Jan 2007 20:44:13 +0100
Message-ID: <6e9345580701051144u2e27cf9y35528e9877c8a35a@mail.gmail.com>


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
Received on Fri Jan 05 2007 - 13:44:13 CST

Original text of this message

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