For example, November, 2007 will almost certainly follow October, 2007, so using Oracle??™s
interval partitioning saves you the effort and creates and maintain new partitions when needed.
Here is an example of a range-partitioned table with four partitions and an interval definition of
one month:
create table order_hist_interval
(order_num NUMBER(15),
cust_id NUMBER(12),
568 Oracle Database 11g DBA Handbook
order_dt date,
order_total NUMBER(10,2)
)
partition by range (order_dt)
interval(numtoyminterval(1,'month'))
(partition p0 values less than (to_date('20051231','YYYYMMDD')),
partition p1 values less than (to_date('20060701','YYYYMMDD')),
partition p2 values less than (to_date('20061231','YYYYMMDD')),
partition p3 values less than (to_date('20070701','YYYYMMDD'))
);
Rows inserted with an ORDER_DT of July 1, 2007, or earlier will reside in one of the four
initial partitions of ORDER_HIST_INTERVAL. Rows inserted with an ORDER_DT after July 1,
2007, will trigger the creation of a new partition with a range of one month each; the upper
bound of each new partition will always be the first of the month, based on the value of the
highest partition??™s upper limit. Looking in the data dictionary, this table looks like a pre??“Oracle
11g range partitioned table:
SQL> select table_name, partition_name, high_value
2 from dba_tab_partitions
3 where table_name = 'ORDER_HIST_INTERVAL';
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',
SQL>
However, suppose you add a row for November 11, 2007, as in this example:
SQL> insert into order_hist_interval
2 values (19581968,1963411,to_date('20071111','YYYYMMDD'),420.
Pages:
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847