Interval Partitioning

Oracle 11g has a new feature that will make life easier for the DBAs. Prior to this release , the DBAs must make sure they create a new partition to accommodate the new incoming dataset .

For example , to facilitate year end processing , they should create additional partitions to move the new year’s data into the database . Failing to do so will create “abend” jobs . They have to reactively create new partitions and re-run the job.Let us create a table , partition by year .

SQL> CREATE TABLE t_partTable created.

2 (
3 salyear INTEGER ,
4 salmount NUMBER(5,2) ,
5 saldesc VARCHAR2(40)
6 )
7 partition BY range
8 (
9 salyear
10 )
11 (
12 partition p_2001 VALUES less than (2001),
13 partition p_2002 VALUES less than (2002),
14 partition p_2003 VALUES less than (2003),
15 partition p_2004 VALUES less than (2004)
16 );

Let us insert few records.

1 row created.

SQL> insert into t_part values ( 2001 , 25.00 , 'Tamil VCD') ;
1 row created.

SQL> insert into t_part values ( 2002 , 30.00 , 'Tamil SVCD') ;
1 row created.

SQL> insert into t_part values ( 2003 , 500.00 , 'Tamil DVD') ;
1 row created.

SQL> insert into t_part values ( 2002 , 40.00 , 'English SVCD') ;
1 row created.

SQL> insert into t_part values ( 2003 , 500.00 , 'English DVD') ;
1 row created.

SQL> commit;
Commit complete.

SQL> exec dbms_stats.gather_table_stats(user , 'T_PART');

PL/SQL procedure successfully completed.

SQL> SELECT table_name,
2 partition_name,
3 num_rows
4 FROM user_tab_partitions
5 WHERE table_name='T_PART';

TABLE_NAME PARTITION_NAME NUM_ROWS
---------- -------------- -------
T_PART      P_2001         1
T_PART      P_2002         1
T_PART      P_2003         2
T_PART      P_2004         2

Let us add a record for the year 2004. As you can see , there is an error as we have not created a partition for the year 2004.

SQL> insert into t_part values ( 2004 , 550.00 , 'French DVD') ;

insert into t_part values ( 2004 , 550.00 , 'French DVD')
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

We can mitigate the above issue by creating a default partition .
Here is the second version of the table.

We can mitigate the above issue by creating a default partition . Here is the second version of the table.

SQL> drop table t_part purge ;

Table dropped.

SQL> create table t_part
2 (
3 salyear integer ,
4 salmount number(5,2) ,
5 saldesc varchar2(40)
6 )
7 partition by range(salyear)
8 (
9 partition p_2001 values less than (2001),
10 partition p_2002 values less than (2002),
11 partition p_2003 values less than (2003),
12 partition p_2004 values less than (2004),
13 partition p_max values less than (MAXVALUE)
14 );

Table created.

SQL> insert into t_part values ( 2000 , 15.00 , 'Tamil VHS') ;
1 row created.

SQL> insert into t_part values ( 2001 , 25.00 , 'Tamil VCD') ;
1 row created.

SQL> insert into t_part values ( 2002 , 30.00 , 'Tamil SVCD') ;
1 row created.

SQL> insert into t_part values ( 2003 , 500.00 , 'Tamil DVD') ;
1 row created.

SQL> insert into t_part values ( 2002 , 40.00 , 'English SVCD') ;
1 row created.

SQL> insert into t_part values ( 2003 , 500.00 , 'English DVD') ;
1 row created.

SQL> insert into t_part values ( 2004 , 550.00 , 'French DVD') ;
1 row created.

SQL> commit;
Commit complete.

SQL> exec dbms_stats.gather_table_stats(user , 'T_PART');
PL/SQL procedure successfully completed.

SQL> select table_name,partition_name,num_rows
2 from user_tab_partitions
3 where table_name='T_PART';

TABLE_NAME PARTITION_NAME NUM_ROWS

---------   -----------  ----------
T_PART       P_2001           1
T_PART       P_2002           1
T_PART       P_2003           2
T_PART       P_2004           2
T_PART       P_MAX            1

As you can see the issue is mitigated … Let us insert a insert for another year .

SQL> insert into t_part values ( 2010 , 950.00 , 'Tamil Blu Ray ') ; SQL> exec dbms_stats.gather_table_stats(user , 'T_PART');

PL/SQL procedure successfully completed.

SQL> select table_name,partition_name,num_rows
2 from user_tab_partitions
3 where table_name='T_PART';

TABLE_NAME PARTITION_NAME NUM_ROWS
--------- ----------- ----------
T_PART      P_2001           1
T_PART      P_2002           1
T_PART      P_2003           2
T_PART      P_2004           2
T_PART      P_MAX            1

The issue is mitigated , but all other data goes into one partition , which lead into other issues. Here is where interval partition comes handy. Let us create third version of the table .

SQL> drop table t_part purge ;

Table dropped.

SQL> create table t_part
2 (
3 salyear integer ,
4 salmount number(5,2) ,
5 saldesc varchar2(40)
6 )
7 partition by range(salyear)
8 interval(1)
9 (
10 partition p_2001 values less than (2001),
11 partition p_2002 values less than (2002),
12 partition p_2003 values less than (2003),
13 partition p_2004 values less than (2004)
14 );

Table created.
SQL> insert into t_part values ( 2000 , 15.00 , 'Tamil VHS') ;

1 row created.

SQL> insert into t_part values ( 2001 , 25.00 , 'Tamil VCD') ;
1 row created.

SQL> insert into t_part values ( 2002 , 30.00 , 'Tamil SVCD') ;
1 row created.

SQL> insert into t_part values ( 2003 , 500.00 , 'Tamil DVD') ;
1 row created.

SQL> insert into t_part values ( 2002 , 40.00 , 'English SVCD') ;
1 row created.

SQL> insert into t_part values ( 2003 , 500.00 , 'English DVD') ;
1 row created.

SQL> insert into t_part values ( 2004 , 550.00 , 'French DVD') ;
1 row created.

SQL> exec dbms_stats.gather_table_stats(user , 'T_PART');
PL/SQL procedure successfully completed.

SQL> select table_name,partition_name,num_rows
2 from user_tab_partitions
3 where table_name='T_PART';

TABLE_NAME PARTITION_NAME NUM_ROWS
--------- ------------- ----------
T_PART       P_2001          1
T_PART       P_2002          1
T_PART       P_2003          2
T_PART       P_2004          2
T_PART       SYS_P62         1

As you can see from the above example , Oracle gracefully accommodated new record and assigned it to a new partition ( system defined ) . Later on , you can rename the partition to user defined one.

SQL> alter table t_part rename partition SYS_P62 to p_2005;

Table altered.

SQL> select table_name,partition_name,num_rows
2 from user_tab_partitions
3 where table_name='T_PART';

TABLE_NAME PARTITION_NAME NUM_ROWS
---------- ------------- ----------
T_PART       P_2001           1
T_PART       P_2002           1
T_PART       P_2003           2
T_PART       P_2004           2
T_PART       P_2005           1

Let us create another record for the year 2010.

SQL> insert into t_part values ( 2010 , 950.00 , 'Tamil Blu Ray ') ;

1 row created.

SQL> exec dbms_stats.gather_table_stats(user , 'T_PART');
PL/SQL procedure successfully completed.

SQL> select table_name,partition_name,num_rows
2 from user_tab_partitions
3 where table_name='T_PART';

TABLE_NAME PARTITION_NAME NUM_ROWS
---------  -------------- ----------
T_PART      P_2001           1
T_PART      P_2002           1
T_PART      P_2003           2
T_PART      P_2004           2
T_PART      P_2005           1
T_PART      SYS_P63          1

Now , Oracle has created another partition for the different year’s data.

Hope you enjoyed this post.

Originally published at https://zahirmohideen.blogspot.com/2011/02/interval-partitioning.html