Interval partitioning
Interval partitioning is a partitioning method introduced in Oracle 11g. This is a helpful addition to range partitioning where Oracle automatically creates a partition when the inserted value exceeds all other partition ranges.
The following restrictions apply:
- You can only specify one partitioning key column, and it must be of NUMBER or DATE type.
- Interval partitioning is NOT supported for index-organized tables.
- You can NOT create a domain index on an interval-partitioned table.
Test case[edit]
Let start it from here:
SQL> conn hr/hr Connected.
Here we create a table with range partition on the SALARY column.
SQL> create table test 2 (sno number(6), 3 last_name varchar2(30), 4 salary number(6)) 5 partition by range(salary) 6 ( 7 partition p1 values less than (5000), 8 partition p2 values less than (10000), 9 partition p3 values less than (15000), 10 partition p4 values less than (20000)); Table created.
Let insert some records into test table.
SQL> insert into test
select employee_id,last_name,salary from employees
where last_name not like '%K%';
101 rows created.
SQL> select table_name,partition_name,num_rows
2 from user_tab_partitions
3 where table_name='TEST';
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
TEST P1
TEST P2
TEST P4
TEST P3
SQL> exec dbms_stats.gather_table_stats('HR','TEST');
PL/SQL procedure successfully completed.
SQL> select table_name,partition_name,num_rows
from user_tab_partitions
where table_name='TEST';
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
TEST P1 48
TEST P2 37
TEST P4 1
TEST P3 15
As we know the data which we inserted obeyed all rules defined for partitions. Let see what happens:
SQL> insert into test
values
(1,'Michel',25000);
insert into test *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
Note the 14400 error.
ORA-14400:
inserted partition key does not map to any partition
Cause:
An attempt was made to insert a record into, a Range or Composite Range object, with a concatenated partition key that is beyond the concatenated partition bound list of the last partition -OR- An attempt was made to insert a record into a List object with a partition key that did not match the literal values specified for any of the partitions.
Action:
Do not insert the key. Or, add a partition capable of accepting the key, Or add values matching the key to a partition specification.
Now in 11g, Oracle introduced new partition type called INTERVAL PARTITIONING. So now I am going to check what the benefits of interval partitioning are.
First I will drop mine existing TEST table.
SQL> drop table test purge; Table dropped. SQL> create table test 2 (sno number(6), 3 last_name varchar2(30), 4 salary number(6)) 5 partition by range(salary) 6 Interval (5000) 7 ( 8 partition p1 values less than (5000), 9 partition p2 values less than (10000), 10 partition p3 values less than (15000), 11 partition p4 values less than (20000)); Table created.
Let check the partition names.
SQL> select table_name,partition_name From user_tab_partitions Where table_name='TEST'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ TEST P4 TEST P1 TEST P2 TEST P3
Here we can see we created four partition during table creation now how oracle will use this INTERVAL. Let's try to insert records into test table.
SQL> insert into test 2 select employee_id,last_name,salary from employees 3 where last_name not like '%K%'; 101 rows created.
I inserted the data which already obeyed the existing partitions limit.
SQL> exec dbms_stats.gather_table_stats('HR','TEST');
PL/SQL procedure successfully completed.
SQL> select table_name,partition_name,num_rows
2 from user_tab_partitions
3 where table_name='TEST';
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ----------------- ----------
TEST P4 1
TEST P1 48
TEST P2 37
TEST P3 15
Now I’m going to insert the data which is not mentioned for any partition.
SQL> insert into test 2 values 3 (1,'Michel',25000); 1 row created. SQL> insert into test 2 values 3 (2,'Michel',30000); 1 row created. SQL> insert into test 2 values 3 (3,'Michel',35000); 1 row created. SQL> insert into test 2 values 3 (4,'Michel',40000); 1 row created.
You can see that this time it didn’t generate the ORA_14400 errors. Let see what oracle did to insert the data over the partitions limit.
Here we go.
SQL> select table_name,partition_name 2 from user_tab_partitions 3 where table_name='TEST'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ TEST P4 TEST SYS_P41 TEST SYS_P42 TEST P1 TEST P2 TEST P3 TEST SYS_P43 TEST SYS_P44 8 rows selected.
I created 4 partitions but now we can see there are total 8 partitions ,four extra partitions with system generated names. Now its clear like water what INTERVAL did, as I specify the INTERVAL limit of (5000) and I inserted 4 records with the interval of 5000 each so oracle created new system generated partition for each that partition which was in the interval of 5000.
SQL> select table_name,partition_name,num_rows
2 from user_tab_partitions
3 where table_name='TEST' order by partition_name;
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
TEST P1 48
TEST P2 37
TEST P3 15
TEST P4 1
TEST SYS_P41 2
TEST SYS_P42 1
TEST SYS_P43 1
TEST SYS_P44 1
8 rows selected.
SQL> select salary from test where salary>20000;
SALARY
----------
25000
25000
30000
35000
40000
We can easily check that salary column have 2 values for 25000 that’s why we have num_rows 2 for SYS_P41.
