Difference between revisions of "Interval partitioning"

From Ittichai Chammavanijakul's Wiki
Jump to navigation Jump to search
 
(3 intermediate revisions by the same user not shown)
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? ==
 +
<pre>
 +
-- 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.
 +
 +
</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>
 +
 
== See Also ==
 
== See Also ==
  
 
[[Category: Database Features]]
 
[[Category: Database Features]]
 
[[Category: Oracle 11g]]
 
[[Category: Oracle 11g]]

Latest revision as of 18:58, 4 March 2013

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')

See Also

Retrieved from "https://ittichaicham.com/wiki/index.php?title=Interval_partitioning&oldid=876"