partitioning pruning issue

I have a partition pruning issue on reporting query which has inner join between time dimension tables and some fact tables.

the fact table are range partition by due_date columns but DIM_TIME_DATE table isn't partitioned.

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?

--

thanks and regards
ujang jaenudin | independent consultant
http://ora62.wordpress.com
http://blog.dbs247.com
--

http://www.freelists.org/webpage/oracle-l Received on Thu Sep 29 2011 - 19:38:19 CDT