Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle Business Intelligence, OLAP and BI training and consulting tips . . .

Click here for more
Oracle News Headlines


Oracle Partitioning And The Time Dimension
March 23, 2005
Mark Rittman

"I have a question about Oracle partitioning and a time dimension. The design we have for the time dimension uses a numeric surrogate key as a primary key in the time dimension, and the corresponding foreign key in the fact table is also a numeric key. However, we currently range partition the fact table by another column in the table that is an actual date column. We don't get any partition pruning when queries involve the time dimension, which I believe is because queries against the time dimension are joining the fact table on a numeric column, while that table is actually partitioned by date.

Should we remove the partitioning on the date column and instead partition on the numeric key column? The purest in me can't quite come to grips with the fact that we would be range partitioning on a numeric "smart" key when what we really want is the range partition by date."

This is an interesting one. If you've read Kimball's Data Warehousing Lifecycle Toolkit, you'll know that the recommendation is to use surrogate keys for all dimension key lookups, including the date dimension. However, as you say, the obvious way to partition your fact table is by date, and yet there now is no "date" column to range partition on, just a date dimension surrogate key.

What I do in these circumstances is either one of two options:

First is to forget the surrogate key rule for the date dimension, and just use a regular date datetype for the date dimension primary key. Dates are stored in an optimised format anyway (so don't take up as much space as the date spelt out in individual characters) and by having real dates in the fact table, it makes range partitioning on load date much more intuitive. Also, when Kimball came up with the surrogate key rules, partitioning wasn't in general use and no doubt now he'd agree with this common sense exception to the rule.

The second option, if we're going for the purist route, is to use a surrogate key for the date dimension, and still range partition it, but instead of using start and end dates you'll just need to use the revelvant time dimension keys that correspond to the start and end of your partition periods. Works just as well, although not quite as intuitive to work with. You'll still get partition pruning on date as the join will be on the column that is used for the range partitioning.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational