Partition integer date key by month

Database
Enthusiast

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

4 REPLIES
Enthusiast

Re: Partition integer date key by month

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

Enthusiast

Re: Partition integer date key by month

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

Enthusiast

Re: Partition integer date key by month

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.

Enthusiast

Re: Partition integer date key by month

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