All Forums Database
goldminer 118 posts Joined 05/09
22 Jul 2014
Partition integer date key by month

Hello All!
 
I am looking for a way to partition an integer by month.  The data type is integer and the values are 20140701, 20140702, etc.  We are joining to sys_calendar.EDW_DATE_T.DATE_KEY in an attempt to get dynamic partition elimination.  Has anybody been able to do this very easily?  I have partitioned by day with the following but am trying to figure out how to convert to a monthly partition:
 
PARTITION BY RANGE_N(POSTING_DT_KEY BETWEEN 20040801 AND 20041231 EACH 1 ,
20050101 AND 20051231 EACH 1 ,
20060101 AND 20061231 EACH 1 ,
20070101 AND 20071231 EACH 1 ,
20080101 AND 20081231 EACH 1 ,
20090101 AND 20091231 EACH 1 ,
20100101 AND 20101231 EACH 1 ,
20110101 AND 20111231 EACH 1 ,
20120101 AND 20121231 EACH 1 ,
20130101 AND 20131231 EACH 1 ,
20140101 AND 20141231 EACH 1 ,
NO RANGE, UNKNOWN)
 
Thanks,
 
Joe

Glass 225 posts Joined 04/10
22 Jul 2014

 
Try this.
 

PARTITION BY RANGE_N
(CAST(POSTING_DT_KEY - 19000000 AS DATE) BETWEEN '2004-08-01' AND '2014-12-31' EACH INTERVAL '1' MONTH ,
NO RANGE, UNKNOWN);

 
Rglass

goldminer 118 posts Joined 05/09
28 Jul 2014

Thank you... yes I did try that but was concerned about the partition being used if a cast is made.  Guess there is only one way to find out.  I will cast as a date and then see if I can get dynamic partition elimination when joining to the date_key in edw_date_t and filtering on calendar_date.
 
Thanks,
 
Joe

Raja_KT 1246 posts Joined 07/09
28 Jul 2014

I  was about to comment on posting_dt_key integer declaration in partition. I think you  may have something in mind about it when you made it integer, since you know the end reqt and data demography better.An explain can give good hint.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

goldminer 118 posts Joined 05/09
29 Jul 2014

Thanks Raja!
 
The architects want to join the fact to the date dimension on the date key and then filter on the date in the date dimension.  The best I can get at that point is dynamic partition elimination.  The date key is an intelligent key in that the integer is yyyymmdd.  I realize I can get static partition elimination if the date is filtered on a date field in the fact table but that is not the design. 
 
Joe

You must sign in to leave a comment.