Oracle INTERVAL partitioning is an extension to the existing RANGE partitioning, and can be used to partition tables for DATE and NUMBER columns.
Below are some examples of working with interval partitioning.
Create table:
create table
pos_data (
start_date DATE,
store_id NUMBER,
inventory_id NUMBER(6),
qty_sold NUMBER(3)
)
PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
);
Table created.
Insert rows in table to generate interval partitions:
select sysdate from dual;
SYSDATE
---------
17-OCT-12
insert into pos_data values (sysdate,1,1,1);
1 row created.
insert into pos_data values (sysdate-30,1,1,1);
1 row created.
SELECT
TABLE_NAME,
PARTITION_NAME,
PARTITION_POSITION,
HIGH_VALUE
FROM
USER_TAB_PARTITIONS
WHERE
TABLE_NAME='POS_DATA'
ORDER BY
PARTITION_NAME;
TABLE_NAME PARTITION_NAME PARTITION_POSITION
------------------------------ ------------------------------ ------------------
HIGH_VALUE
--------------------------------------------------------------------------------
POS_DATA POS_DATA_P2 1
TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POS_DATA POS_DATA_P3 2
TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POS_DATA SYS_P281 4
TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TABLE_NAME PARTITION_NAME PARTITION_POSITION
------------------------------ ------------------------------ ------------------
HIGH_VALUE
--------------------------------------------------------------------------------
POS_DATA SYS_P282 3
TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
Below are some examples of working with interval partitioning.
Create table:
create table
pos_data (
start_date DATE,
store_id NUMBER,
inventory_id NUMBER(6),
qty_sold NUMBER(3)
)
PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
);
Table created.
View partitions for the table:
SELECT
TABLE_NAME,
PARTITION_NAME,
PARTITION_POSITION,
HIGH_VALUE
FROM
USER_TAB_PARTITIONS
WHERE
TABLE_NAME='POS_DATA'
ORDER BY
PARTITION_NAME;
TABLE_NAME PARTITION_NAME PARTITION_POSITION
------------------------------ ------------------------------ ------------------
HIGH_VALUE
--------------------------------------------------------------------------------
POS_DATA POS_DATA_P2 1
TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POS_DATA POS_DATA_P3 2
TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
Insert rows in table to generate interval partitions:
select sysdate from dual;
SYSDATE
---------
17-OCT-12
insert into pos_data values (sysdate,1,1,1);
1 row created.
insert into pos_data values (sysdate-30,1,1,1);
1 row created.
SELECT
TABLE_NAME,
PARTITION_NAME,
PARTITION_POSITION,
HIGH_VALUE
FROM
USER_TAB_PARTITIONS
WHERE
TABLE_NAME='POS_DATA'
ORDER BY
PARTITION_NAME;
TABLE_NAME PARTITION_NAME PARTITION_POSITION
------------------------------ ------------------------------ ------------------
HIGH_VALUE
--------------------------------------------------------------------------------
POS_DATA POS_DATA_P2 1
TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POS_DATA POS_DATA_P3 2
TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POS_DATA SYS_P281 4
TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TABLE_NAME PARTITION_NAME PARTITION_POSITION
------------------------------ ------------------------------ ------------------
HIGH_VALUE
--------------------------------------------------------------------------------
POS_DATA SYS_P282 3
TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
Select data from interval partition:
select * from pos_data partition for (to_date('15-10-2012','dd-mm-yyyy'));
START_DAT STORE_ID INVENTORY_ID QTY_SOLD
--------- ---------- ------------ ----------
17-OCT-12 1 1 1
Drop interval partition
alter table pos_data drop partition for (to_date('15-09-2012','dd-mm-yyyy'));
Table altered.
SELECT
TABLE_NAME,
PARTITION_NAME,
INTERVAL,
PARTITION_POSITION,
HIGH_VALUE
FROM
USER_TAB_PARTITIONS
WHERE
TABLE_NAME='POS_DATA'
ORDER BY
PARTITION_NAME;
TABLE_NAME PARTITION_NAME INT
------------------------------ ------------------------------ ---
PARTITION_POSITION
------------------
HIGH_VALUE
--------------------------------------------------------------------------------
POS_DATA POS_DATA_P2 NO
1
TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POS_DATA POS_DATA_P3 NO
2
TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TABLE_NAME PARTITION_NAME INT
------------------------------ ------------------------------ ---
PARTITION_POSITION
------------------
HIGH_VALUE
--------------------------------------------------------------------------------
POS_DATA SYS_P281 YES
3
TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
alter table pos_data drop partition for (to_date('15-10-2012','dd-mm-yyyy'));
Table altered.
alter table pos_data drop partition pos_data_p2;
Table altered.
SELECT
TABLE_NAME,
PARTITION_NAME,
INTERVAL,
PARTITION_POSITION,
HIGH_VALUE
FROM
USER_TAB_PARTITIONS
WHERE
TABLE_NAME='POS_DATA'
ORDER BY
PARTITION_NAME;
TABLE_NAME PARTITION_NAME INT
------------------------------ ------------------------------ ---
PARTITION_POSITION
------------------
HIGH_VALUE
--------------------------------------------------------------------------------
POS_DATA POS_DATA_P3 NO
1
TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
alter table pos_data drop partition pos_data_p3;
alter table pos_data drop partition pos_data_p3
*
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped
Change interval partitioning range:
alter table pos_data set INTERVAL(NUMTOYMINTERVAL(3, 'MONTH'));
Table altered.
insert into pos_data values (sysdate - 90,1,1,1);
1 row created.
SQL> SELECT
TABLE_NAME,
PARTITION_NAME,
INTERVAL,
PARTITION_POSITION,
HIGH_VALUE
FROM
USER_TAB_PARTITIONS
WHERE
TABLE_NAME='POS_DATA'
ORDER BY
PARTITION_NAME;
TABLE_NAME PARTITION_NAME INT
------------------------------ ------------------------------ ---
PARTITION_POSITION
------------------
HIGH_VALUE
--------------------------------------------------------------------------------
POS_DATA POS_DATA_P3 NO
1
TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POS_DATA SYS_P283 NO
3
TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TABLE_NAME PARTITION_NAME INT
------------------------------ ------------------------------ ---
PARTITION_POSITION
------------------
HIGH_VALUE
--------------------------------------------------------------------------------
POS_DATA SYS_P284 NO
2
TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
select * from pos_data partition for (to_date('15-09-2012','dd-mm-yyyy'));
START_DAT STORE_ID INVENTORY_ID QTY_SOLD
--------- ---------- ------------ ----------
17-SEP-12 1 1 1
19-JUL-12 1 1 1
Deactivate interval partitioning, table remains only with range partitioning:
alter table pos_data drop partition for (to_date('15-10-2012','dd-mm-yyyy'));
Table altered.
alter table pos_data set interval();
Table altered.
SELECT
TABLE_NAME,
PARTITION_NAME,
INTERVAL,
PARTITION_POSITION,
HIGH_VALUE
FROM
USER_TAB_PARTITIONS
WHERE
TABLE_NAME='POS_DATA'
ORDER BY
PARTITION_NAME;
TABLE_NAME PARTITION_NAME INT
------------------------------ ------------------------------ ---
PARTITION_POSITION
------------------
HIGH_VALUE
--------------------------------------------------------------------------------
POS_DATA POS_DATA_P3 NO
1
TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POS_DATA SYS_P284 NO
2
TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TABLE_NAME PARTITION_NAME INT
------------------------------ ------------------------------ ---
PARTITION_POSITION
------------------
HIGH_VALUE
--------------------------------------------------------------------------------
insert into pos_data values (sysdate,1,1,1);
insert into pos_data values (sysdate,1,1,1)
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
Reactivate interval partitioning:
alter table pos_data set INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));
Table altered.
insert into pos_data values (sysdate,1,1,1);
1 row created.
SELECT
TABLE_NAME,
PARTITION_NAME,
INTERVAL,
PARTITION_POSITION,
HIGH_VALUE
FROM
USER_TAB_PARTITIONS
WHERE
TABLE_NAME='POS_DATA'
ORDER BY
PARTITION_NAME;
TABLE_NAME PARTITION_NAME INT
------------------------------ ------------------------------ ---
PARTITION_POSITION
------------------
HIGH_VALUE
--------------------------------------------------------------------------------
POS_DATA POS_DATA_P3 NO
1
TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POS_DATA SYS_P284 NO
2
TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
TABLE_NAME PARTITION_NAME INT
------------------------------ ------------------------------ ---
PARTITION_POSITION
------------------
HIGH_VALUE
--------------------------------------------------------------------------------
POS_DATA SYS_P285 YES
3
TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
View interval size for table:
SELECT TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,
PARTITIONING_KEY_COUNT,STATUS,INTERVAL
from USER_PART_TABLES
where table_name='POS_DATA';
TABLE_NAME PARTITION PARTITION_COUNT PARTITIONING_KEY_COUNT
------------------------------ --------- --------------- ----------------------
STATUS
--------
INTERVAL
--------------------------------------------------------------------------------
POS_DATA RANGE 1048575 1
VALID
NUMTOYMINTERVAL(1,'MONTH')
No comments:
Post a Comment