Two significant enhancements to partitioning introduced in Oracle 11g are reference partitioning and interval partitioning. Reference partitioning allows you to create a partition based on a column that is not in the table being partitioned, but rather is a foreign key reference to a different table. This can be useful for partitioning related tables in related ways, even if they do not share the same columns. Interval partitioning frees the DBA from having to manage the size of partitions that are defined with MAXVALUE; instead, new partitions are created automatically based on the appearance of new numeric or date values for a partition key.
Reference Partitioning
For example, an INVOICE_HEADERS table may include an INVOICE_DATE column, tracking the date on which an invoice was issued. The line items for invoices may be stored in an INVOICE_LINE_ITEMS table—and from a normalization perspective, it would not be appropriate to store the INVOICE_DATE at the line-item level. However, if the INVOICE_DATE column is not in the INVOICE_LINE_ITEMS table, how can you partition INVOICE_LINE_ITEMS by the invoice date?
You can range-partition INVOICE_HEADERS by the INVOICE_DATE column on a monthly basis. Suppose the INVOICE_LINE_ITEMS table does not have a DATE datatype column—but it has a foreign key on the INVOICE_NUMBER column back to the INVOICE_HEADERS table. How can you partition it?
As of Oracle 11g, you can use a reference partition to solve this dilemma. The table creation for the INVOICE_LINE_ITEMS table (assuming the INVOICE_HEADERS table already exists and is partitioned) using a reference partition is shown in the following listing:
When you create the INVOICE_LINE_ITEMS table, it will be partitioned by the same column as is used to partition the INVOICE_HEADERS table, even if that column (INVOICE_DATE) is not in the INVOICE_LINE_ITEMS table. The two will be partitioned in sync.
This may seem to be a minor advance, but it is highly significant for effective management of data as it ages. As time goes by, you will need to be able to archive and delete related data from multiple tables in a consistent fashion. You will need to be able to remove data without worrying that there are dangling references that will invalidate referential integrity along the way. By using reference partitions, you are unifying the ways in which the data is divided across multiple tables, and that significantly eases the maintenance burden.
Interval Partitioning
The other new partition type available starting with Oracle 11g is the interval partition. In interval partitioning, you do not specify the specific range values for each partition; instead, you specify the duration of the interval. That is, instead of specifying that partition 1 ends on January 31 and partition 2 ends on February 29, you specify that each partition is one month long. When a new row is inserted, Oracle determines which partition to place the row in based on the interval definition. If you have not created a partition for that month, the database automatically creates a new one.
Use interval partitions with caution. You need to perform the data-value constraint checks yourself prior to inserting the row. If you do not, you may end up with unwanted partitions that were created simply because a data entry person mistakenly entered “2098” for a year instead of “2008.”
Note that if you rely on interval partitioning automatically creating your partitions for you, your application developers cannot rely on consistent partition names because Oracle will create a system-generated name for each new partition it automatically creates.
Leave a Reply