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

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.

• 100% Practical Training

• Hands
  On
  Training

• 100%
  Job
  Gurantee

Interested to be a trainer?

jobs@greenstech.in

For corporate Training

corp@greenstech.in

Copyright © 2018 Greens Technologys. All Rights Reserved. Privacy Policy