Oracle 11g Partitioning

An example of  how the new  Parent/Child partitioning  works in Oracle 11g

Imagine a standard master/detail set of tables for orders.

  • An ORDER table with columns “order no | date | customer no | some more fields”
  • An ORDER_DETAIL table with columns “order no | some detail fields”

 ORDER -> ORDER_DETAIL would be one -> many, linked on “order no”

If you want to report on orders daily weekly or monthly you might partition by date. But the ORDER_DETAIL table has no date, so how can you partition it to match the ORDER table? Traditionally you had to add a redundant date field and keep it in sync with the date in the master record in ORDER.

Oracle 11g allows you declare the master/detail relationship and the database will automatically partition ORDER_DETAIL table based on the partitions in ORDER. All of the ORDER_DETAIL child records will be split off into separate partitions matching the layout of the parent record partitions in ORDER. The database maintains this automatically as the partition maintenance is done on the parent table.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: