ORACLE11G Interval Partitioning(区间分区)

表空间自动增长，降低了表空间管理，对一些可以自动增加段的表可以由他们自动管理。

SQL> CREATE TABLE INTERVAL_TAB (
  2 id NUMBER,
  3 code VARCHAR2(10),
  4 description VARCHAR2(50),
  5 created_date DATE
  6 )
  7 PARTITION BY RANGE (created_date)
  8 INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
  9 (
 10 PARTITION part_01 values LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
 11 );

Table created.

SQL> EXEC DBMS_STATS.gather_table_stats('SA','INTERVAL_TAB');

PL/SQL procedure successfully completed.
SQL>
SQL> SELECT table_name, partition_name, high_value, num_rows
  2 FROM user_tab_partitions
  3 ORDER BY table_name, partition_name;

TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------

INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 0
                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
                                                                                                                        
LIST_HASH_TAB PART_AA 'AA' 20
LIST_HASH_TAB PART_BB 'BB' 20

SQL>
SQL> INSERT INTO INTERVAL_TAB VALUES (1, 'ONE', 'One', TO_DATE('16-OCT-2007', 'DD-MON-YYYY'));

1 row created.

SQL> INSERT INTO INTERVAL_TAB VALUES (2, 'TWO', 'Two', TO_DATE('31-OCT-2007', 'DD-MON-YYYY'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rows
  2 FROM user_tab_partitions
  3 ORDER BY table_name, partition_name;

TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
                                                                                                                        
LIST_HASH_TAB PART_AA 'AA' 20
LIST_HASH_TAB PART_BB 'BB' 20

SQL> INSERT INTO INTERVAL_TAB VALUES (3, 'THREE', 'Three', TO_DATE('01-NOV-2007', 'DD-MON-YYYY'));

1 row created.

SQL> INSERT INTO INTERVAL_TAB VALUES (4, 'FOUR', 'Four', TO_DATE('30-NOV-2007', 'DD-MON-YYYY'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT table_name, partition_name, high_value, num_rows
  2 FROM user_tab_partitions
  3 ORDER BY table_name, partition_name;

TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------

INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
                                                                                                                        
INTERVAL_TAB SYS_P41 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M 2
                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
                                                                                                                        
LIST_HASH_TAB PART_AA 'AA' 20
LIST_HASH_TAB PART_BB 'BB' 20

SQL> INSERT INTO INTERVAL_TAB VALUES (5, 'FIVE', 'Five', TO_DATE('01-JAN-2008', 'DD-MON-YYYY'));

1 row created.

SQL> INSERT INTO INTERVAL_TAB VALUES (4, 'FOUR', 'Four', TO_DATE('31-JAN-2008', 'DD-MON-YYYY'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT table_name, partition_name, high_value, num_rows
  2 FROM user_tab_partitions
  3 ORDER BY table_name, partition_name;

TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------

INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
                                                                                                                        
INTERVAL_TAB SYS_P41 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M 2
                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
                                                                                                                        
INTERVAL_TAB SYS_P42 TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M 2
                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
                                                                                                                        
LIST_HASH_TAB PART_AA 'AA' 20
LIST_HASH_TAB PART_BB 'BB' 20

SQL> INSERT INTO INTERVAL_TAB VALUES (7, 'SEVEN', 'Seven', TO_DATE('01-DEC-2007', 'DD-MON-YYYY'));

1 row created.

SQL> INSERT INTO INTERVAL_TAB VALUES (8, 'EIGHT', 'Eight', TO_DATE('31-DEC-2007', 'DD-MON-YYYY'));

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name, high_value, num_rows
  2 FROM user_tab_partitions
  3 ORDER BY table_name, partition_name;

TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
                                                                                                                        
INTERVAL_TAB SYS_P41 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M 2
                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
                                                                                                                        
INTERVAL_TAB SYS_P42 TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M 2
                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
                                                                                                                        
INTERVAL_TAB SYS_P43 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-M 2
                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


区间分区仅限于一个单一的分区键必须是一个数值或日期范围。
在表被创建时，必须要初始化一个分区
区间分区不支持索引组织表
不能在区间分区表上创建domain索引
区间分区能用于按逐渐自动增长的机制分区，但是不能设置分区级别
不能再去见分区表上设置maxvalue
partition 列不能为空