Database Design

Interval partitioned tables

User Rating:  / 0
PoorBest 
Parent Category: Articles
Created on Thursday, 18 March 2010 22:47
Last Updated on Monday, 12 March 2012 13:54
Published on Thursday, 18 March 2010 22:47
Written by Guy Lambregts
Hits: 4281

Interval Partitioned tables

 

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.

create table TABLE_TR1 (

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

alter table TABLE_TR1 add constraint PK_TR1 primary key (TR1_ID);
create sequence SEQ_TR1 cache 1000;


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

 

create table TABLE_TR2 (

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

create sequence SEQ_TR2 cache 1000;


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

1 row created.

 

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_ID TR2_TRANS TR2_TRANSACTIONVALUE

TR2_ISINCODE


      1001 30-APR-09                14.01

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

alter table TABLE_TI1 add constraint PK_TI1 primary key (TI1_ID);
create sequence SEQ_TI1 cache 1000;

 

SQL> insert into table_ti1 values (seq_ti1.nextval,sysdate,14.01,'BE0003884047');
1 row created.
SQL> set long 1000

SQL> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='TABLE_TI1';

TABLE_NAME                     PARTITION_NAME

HIGH_VALUE


TABLESPACE_NAME

TABLE_TI1                      TR1_P0109
TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
PM

TABLE_TI1                      TR1_P0209

TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
PM

TABLE_TI1                      TR1_P0309

TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
PM

TABLE_TI1                      SYS_P41

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 ?

create table TABLE_TI2 (

   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.

 

alter table table_ti2 set store in (PM1 , PM2);