Re: partitioning pruning issue

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

Other related posts: