partitioning pruning issue
- From: Ujang Jaenudin <ujang.jaenudin@xxxxxxxxx>
- To: Oracle Discussion List <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Sep 2011 07:38:19 +0700
lists,
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
