Re: partitioning pruning issue

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Fri, 30 Sep 2011 14:17:54 +0200
Message-ID: <4E85B372.9000002_at_roughsea.com>



On 09/30/2011 02:38 AM, Ujang Jaenudin wrote:
> this predicate, pruning will not work on fact tables:
> AND (( (TO_CHAR(DIM_TIME_DATE.due_date, 'yyyymm') = '201106') ))
>
> I think this is make sense, due to implicit conversion,
> oracle can not understand which partitions to prune.
>
>
> Change the above predicate with this,
> prune does work on fact tables.
>
> AND ((DIM_TIME_DATE.due_date between to_date('2011-06-01 00:00:00','yyyy-mm-
> dd hh24:mi:ss') and to_date('2011-06-30 23:59:59','yyyy-mm-dd hh24:mi:ss') ))
>
>
> is this 10.2.0.1.0 limitation?
> has anyone overcome this issue on the newer patchset or version?
>

It has nothing to do with a limitation. Think about this: partition pruning is a predictive operation; what I mean is that when you get the date, you know from its value that it is useless to search this or this partition of the table because you simply cannot find related rows there.

Your condition says "when I apply this expression to my date, then it must match this value". How do you want a DBMS to predict anything? It must fetch the date values, compute the expression and check whether the condition is satisfied or not. No partition can be pruned. Your expression might as well be

AND (( (TO_CHAR(DIM_TIME_DATE.due_date, 'dd') = '15') ))

to fetch all the rows that are related to a 15th (of any month of any year) - a case where it is obvious that all partitions participate to the query. The optimizer would have to be damn intelligent to differentiate my case above from yours ...

With your rewrite, prediction works. It's very similar to what occurs with indexes, actually.

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>


--
http://www.freelists.org/webpage/oracle-l
Received on Fri Sep 30 2011 - 07:17:54 CDT

Original text of this message