Interval partitioning
Jump to navigation
Jump to search
Contents
What is interval partitioning?
Samples
- Create a RANGE-partitioned table with one partition.
CREATE TABLE SALES ( PRODUCT_NAME VARCHAR2(50), TRANSACTION_DATE DATE, SALE_PRICE NUMBER(10,2), AMOUNT_SOLD NUMBER ) PARTITION BY RANGE (TRANSACTION_DATE) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) ( PARTITION P1 values less than (TO_DATE('03-01-2011','MM-DD-YYYY')) ); Table created
- Table can be created on multiple tablespaces if needed.
CREATE TABLE SALES ( PRODUCT_NAME VARCHAR2(50), TRANSACTION_DATE DATE, SALE_PRICE NUMBER(10,2), AMOUNT_SOLD NUMBER ) PARTITION BY RANGE (TRANSACTION_DATE) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) STORE IN (tbs1, tbs2, tbs3, tbs4) ( PARTITION P1 values less than (TO_DATE('03-01-2011','MM-DD-YYYY')) ); Table created
- Verify the partition creation.
SQL> column TABLE_NAME format A10 SQL> column PARTITION_NAME format A8 SQL> SELECT table_name, partition_name, high_value FROM user_tab_partitions WHERE table_name='SALES'; TABLE_NAME PARTITIO HIGH_VALUE ---------- -------- -------------------------------------------------------------------------------- SALES P1 TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
- Insert a value within the created partition -> No new partition created.
SQL> insert into SALES values ('XOOM','01-FEB-11', 600, 30000); 1 row inserted SQL> SELECT table_name, partition_name, high_value FROM user_tab_partitions 2 WHERE table_name='SALES'; TABLE_NAME PARTITIO HIGH_VALUE ---------- -------- -------------------------------------------------------------------------------- SALES P1 TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
- Insert a value higher than the threshold of the initial partition. Expected a new partition with new one-month range.
SQL> insert into SALES values ('DROID','15-MAR-11', 20, 50000); 1 row inserted SQL> SELECT table_name, partition_name, high_value FROM user_tab_partitions 2 WHERE table_name='SALES'; TABLE_NAME PARTITIO HIGH_VALUE ---------- -------- -------------------------------------------------------------------------------- SALES P1 TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA' SALES SYS_P81 TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
- Insert a value couple months in advance. New partition is created. However, there is a gap still UN-MATERIALIZED.
SQL> insert into SALES values ('DROID2','15-JUL-11', 30, 70000); 1 row inserted SQL> SELECT table_name, partition_name, high_value FROM user_tab_partitions 2 WHERE table_name='SALES'; TABLE_NAME PARTITIO HIGH_VALUE ---------- -------- -------------------------------------------------------------------------------- SALES P1 TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA' SALES SYS_P81 TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA' SALES SYS_P82 TO_DATE(' 2011-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
- Insert a value in between that un-materialized gap.
SQL> insert into SALES values ('DROID PRO','15-JUN-11', 300, 65000); 1 row inserted SQL> SELECT table_name, partition_name, high_value FROM user_tab_partitions 2 WHERE table_name='SALES'; TABLE_NAME PARTITIO HIGH_VALUE ---------- -------- -------------------------------------------------------------------------------- SALES P1 TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA' SALES SYS_P81 TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA' SALES SYS_P83 TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA' SALES SYS_P82 TO_DATE(' 2011-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'