Difference between revisions of "Interval partitioning"
Jump to navigation
Jump to search
Line 1: | Line 1: | ||
== What is interval partitioning? == | == What is interval partitioning? == | ||
== Samples == | == Samples == | ||
+ | |||
+ | * Create a RANGE-partitioned table with one partition. | ||
+ | <pre> | ||
+ | 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 | ||
+ | </pre> | ||
+ | * Table can be created on multiple tablespaces if needed. | ||
+ | <pre> | ||
+ | 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 | ||
+ | </pre> | ||
+ | |||
+ | * Verify the partition creation. | ||
+ | <pre> | ||
+ | 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' | ||
+ | |||
+ | </pre> | ||
+ | * Insert a value within the created partition -> No new partition created. | ||
+ | <pre> | ||
+ | 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' | ||
+ | </pre> | ||
+ | |||
+ | * Insert a value higher than the threshold of the initial partition. Expected a new partition with new one-month range. | ||
+ | <pre> | ||
+ | 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' | ||
+ | |||
+ | </pre> | ||
+ | * Insert a value couple months in advance. New partition is created. However, there is a gap still UN-MATERIALIZED. | ||
+ | <pre> | ||
+ | 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' | ||
+ | </pre> | ||
+ | * Insert a value in between that un-materialized gap. | ||
+ | <pre> | ||
+ | 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' | ||
+ | </pre> | ||
+ | |||
== Can I change the interval after partitions are created already? == | == Can I change the interval after partitions are created already? == | ||
== See Also == | == See Also == |
Revision as of 10:37, 10 March 2011
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'