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.
