Difference between revisions of "Interval partitioning"
Jump to navigation
Jump to search
Line 108: | Line 108: | ||
SALES SYS_P82 TO_DATE(' 2011-08-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> | </pre> | ||
− | + | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
== Can I change the interval after partitions are created already? == | == Can I change the interval after partitions are created already? == | ||
<pre> | <pre> | ||
Line 131: | Line 121: | ||
Table altered. | Table altered. | ||
+ | </pre> | ||
+ | |||
+ | * Check whether or not the table is using the INTERVAL partition. | ||
+ | <pre> | ||
+ | SQL> SELECT table_name, INTERVAL FROM user_part_tables | ||
+ | where table_name = 'SALES'; | ||
+ | |||
+ | TABLE_NAME INTERVAL | ||
+ | ---------------- ------------------------------- | ||
+ | SALES NUMTOYMINTERVAL(1,'MONTH') | ||
</pre> | </pre> | ||
== See Also == | == See Also == |
Revision as of 13:02, 11 December 2012
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.
- 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')