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'
Can I change the interval after partitions are created already?
-- To disable the INTERVAL partition SQL> alter table SALES set interval (); Table altered. -- To enable the INTERVAL partition SQL> alter table SALES set interval (NUMTOYMINTERVAL(1,'MONTH')); Table altered. -- To switch it back to the range partition SQl> alter table SALES set interval(); Table altered.
- Check whether or not the table is using the INTERVAL partition.
SQL> SELECT table_name, INTERVAL FROM user_part_tables where table_name = 'SALES'; TABLE_NAME INTERVAL ---------------- ------------------------------- SALES NUMTOYMINTERVAL(1,'MONTH')