Re: partitioning pruning issue
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: ujang.jaenudin@xxxxxxxxx
- Date: Fri, 30 Sep 2011 14:17:54 +0200
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>
--
//www.freelists.org/webpage/oracle-l
Other related posts: