Partition Exchange in Oracle 12.2

There are many new extensions for Partitioning in Oracle 12c Release 2 (12.2). One of them is the CREATE TABLE FOR EXCHANGE statement. Some years ago, I wished to have this feature for a particular problem with hidden columns.

Continue reading


Reference Partitioning on Star Schema: A Good Idea?

In my last blog post, I noticed that surrogate keys on a time dimension cannot be combined with partitioned fact tables in a star schema. Actually, there is a way to do that: With reference partitioning it is possible to implement monthly partitioned fact tables based on a surrogate time key. But is this a good idea?

Continue reading

Time Dimension Keys and Partitioning

In the physical design of a star schema, it is recommended to use surrogate keys for the primary keys of dimension tables. Except for the time dimension. In Oracle data warehouses it is common to use a DATE column as the primary key. The reason for this decision and several alternative solutions are described in this post.

Continue reading