11);
1 row created.
SQL>
There is now a new partition, as you can see when you query DBA_TAB_PARTITIONS again:
SQL> select table_name, partition_name, high_value
2 from dba_tab_partitions
3 where table_name = 'ORDER_HIST_INTERVAL';
Chapter 16: Managing Large Databases 569
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
HIGH_VALUE
---------------------------------------------------------------------
ORDER_HIST_INTERVAL P0
TO_DATE(' 2005-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
ORDER_HIST_INTERVAL P1
TO_DATE(' 2006-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'
ORDER_HIST_INTERVAL P2
TO_DATE(' 2006-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
ORDER_HIST_INTERVAL P3
TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
ORDER_HIST_INTERVAL SYS_P41
TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
SQL>
Note that partitions for July, August, September, and October will not be created until order
history rows are inserted containing dates within those months.
Using Reference Partitioning Reference partitioning leverages the parent-child relationships
between tables to optimize partition characteristics and ease maintenance for tables that are
frequently joined. In this example, the partitioning defined for the parent table ORDER_HIST
is inherited by the ORDER_ITEM_HIST table:
create table order_hist
(order_num NUMBER(15),
cust_id NUMBER(12),
order_dt date,
order_total NUMBER(10,2),
constraint order_hist_pk primary key(order_num)
)
partition by range (order_dt)
(partition q1_2007 values less than (to_date('20070401','YYYYMMDD')),
partition q2_2007 values less than (to_date('20070701','YYYYMMDD')),
partition q3_2007 values less than (to_date('20071001','YYYYMMDD')),
partition q4_2007 values less than (to_date('20080101','YYYYMMDD'))
)
;
create table order_item_hist
(order_num number(15),
line_item_num number(3),
product_num number(10),
item_price number(8,2),
item_qty number(8),
constraint order_item_hist_fk
foreign key (order_num) references order_hist(order_num)
)
partition by reference(order_item_hist_fk)
;
570 Oracle Database 11g DBA Handbook
Oracle automatically creates corresponding partitions with the same name for the ORDER_
ITEM_HIST as in ORDER_HIST.
Pages:
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848