Range partitioning is as old as Methusalem. The below listed DDL compiles a table of which the partition key is a date column.
Partitions can be created in different tablespaces. Tablespaces containing old data can be put offline. Queries with the partition key in the predicate are subject to partitioning pruning. Administration off large tables and indexes becomes easier. The DBA tasks are done at the partition level.
Who has ever moved a 20G index from one tablespace to another will understand what we speak about.
TR1_ID NUMBER(18) not null,
TR1_TRANSACTIONDATE DATE default SYSDATE not null,
TR1_TRANSACTIONVALUE number(10,2) not null,
TR1_ISINCODE varchar2(100) )
partition by range (TR1_TRANSACTIONDATE)
( partition TR1_P0109 values less than ( TO_DATE('01/02/2009','DD/MM/YYYY') ),
partition TR1_P0209 values less than ( TO_DATE('01/03/2009','DD/MM/YYYY') ),
partition TR1_P0309 values less than ( TO_DATE('01/04/2009','DD/MM/YYYY') )
);
Partitions can be added online so every month we create the partition(s) for the next month(s). But what happens when we forget it ? What happens when our DBA gets ill for a while ?
SQL> insert into table_tr1 values (seq_tr1.nextval,sysdate,14.01,'BE0003884047');
insert into table_tr1 values (seq_tr1.nextval,sysdate,14.01,'BE0003884047')
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
The new records can' t be inserted. Oracle allows us to create a range partitioned table which the below syntax
TR2_ID NUMBER(18) not null,
TR2_TRANSACTIONDATE DATE default SYSDATE not null,
TR2_TRANSACTIONVALUE number(10,2) not null,
TR2_ISINCODE varchar2(100) )
partition by range (TR2_TRANSACTIONDATE)
( partition TR2_P0109 values less than ( TO_DATE('01/02/2009','DD/MM/YYYY') ),
partition TR2_P0209 values less than ( TO_DATE('01/03/2009','DD/MM/YYYY') ),
partition TR2_P0309 values less than ( TO_DATE('01/04/2009','DD/MM/YYYY') ),
partition TR2_P0409 values less than (maxvalue)
);
alter table TABLE_TR2 add constraint PK_TR2 primary key (TR2_ID);
What would happen when our DBA gets ill for a while in this case ?
SQL> insert into table_tr2 values (seq_tr2.nextval,sysdate,14.01,'BE0003884047');
The new data in inserted anyway into partition TR2_P0409. Even when our DBA is ill for months business processing can go on.
SQL> select * from table_tr2 partition (TR2_P0409);
TR2_ISINCODE
BE0003884047
But when our DBA comes back, he or she has some work to do. The partition TR2_P0409 contains data for several months.
What happened with the benefits off partitioning when almost all the data is inserted into one partition anyway ?
Agreed we can create a large amount of partitons in advance, but how many and how big ? Oracle 11G comes with a possible solutions : interval partitioning. Partitions gets automatically created.
Let' s show with a similar example
create table TABLE_TI1 (
TI1_ID NUMBER(18) not null,
TI1_TRANSACTIONDATE DATE default SYSDATE not null,
TI1_TRANSACTIONVALUE number(10,2) not null,
TI1_ISINCODE varchar2(100) )
partition by range (TI1_TRANSACTIONDATE)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( partition TR1_P0109 values less than ( TO_DATE('01/02/2009','DD/MM/YYYY') ),
partition TR1_P0209 values less than ( TO_DATE('01/03/2009','DD/MM/YYYY') ),
partition TR1_P0309 values less than ( TO_DATE('01/04/2009','DD/MM/YYYY') )
);
SQL> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='TABLE_TI1';
HIGH_VALUE
TABLE_TI1 TR1_P0109
TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
PM
TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
PM
TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
PM
TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
PM
So an additional partition is automatically created. Our table is range partitioned, we have a partition per month
1. there is no need to create lots of partitions in advance.
2. our DBA can get ill or is allowed to forget now and then to create partitions
But there is more. What when we wanna have created our partitons in different tablespaces ?
TI2_ID NUMBER(18) not null,
TI2_TRANSACTIONDATE DATE default SYSDATE not null,
TI2_TRANSACTIONVALUE number(10,2) not null,
TI2_ISINCODE varchar2(100) )
partition by range (TI2_TRANSACTIONDATE)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
STORE IN ( PM1 , PM2 , PM3 )
( partition TI2_P0109 values less than ( TO_DATE('01/02/2009','DD/MM/YYYY') ),
partition TI2_P0209 values less than ( TO_DATE('01/03/2009','DD/MM/YYYY') ),
partition TI2_P0309 values less than ( TO_DATE('01/04/2009','DD/MM/YYYY') )
);
Tablespaces PM1, PM2 and PM3 are used in a round robbin way.
The below syntax can be used to alter the storage attributes.