A Brief Review of Interval Partitioning

By -

 With the introduction of interval partitioning in Oracle Database 11g, the need to manually add partitions when inserting data outside the defined range was eliminated. This article briefly reviews several important aspects of interval partitioning.

Point 1: Requirement for an Initial Range Partition

To use range-interval partitioning, at least one range partition must be defined:

SQL> create table tbl (

     id number,

     name varchar2(10),

     date_        DATE

)

PARTITION BY RANGE (date_)

 INTERVAL(NUMTOYMINTERVAL(1, ‘YEAR’))

(

   PARTITION p1 VALUES LESS THAN (TO_DATE(‘4-6-2001’, ‘DD-MM-YYYY’))

);

After new partitions are added automatically, you might decide to drop partition p1 (which is the only range partition):

SQL> insert into tbl values(1,’test’,TO_DATE(‘4-6-2002‘, ‘DD-MM-YYYY’));

1 row inserted

SQL> insert into tbl values(1,’test’,TO_DATE(‘4-6-2003‘, ‘DD-MM-YYYY’));

1 row inserted

SQL> select PARTITION_NAME,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME   INTERVAL

—————- ——–

P1               NO

SYS_P701 YES

SYS_P702         YES

SQL> alter table TBL drop partition p1;

ORA-14758: Last partition in the range section cannot be dropped

In Oracle 11g, the first range partition cannot be dropped unless another range partition exists. To drop it, all interval partitions must first be converted to non-interval partitions:

SQL> alter table tbl set interval( numtoyminterval(1,’YEAR’));

Table altered

SQL> select PARTITION_NAME,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME   INTERVAL

—————- ——–

P1               NO

SYS_P701         NO

SYS_P702         NO

Now the p1 partition can be dropped:

SQL> alter table TBL drop partition p1;

Table altered

In Oracle 12c Release 2 and later, this behavior changed. The first range partition can be dropped directly, and Oracle automatically manages the transition:

SQL> select PARTITION_NAME,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       INTERVAL

——————– ——–

P1                   NO

SYS_P17415           YES

SYS_P17416           YES

SQL> alter table TBL drop partition p1;

Table altered

SQL> select PARTITION_NAME,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       INTERVAL

——————– ——–

SYS_P17415           NO

SYS_P17416           YES

Point 2: Behavior of Automatically Created Interval Partitions

Consider a table partitioned using a range-interval strategy:

SQL> create table tbl (

     id number,

     name varchar2(10),

     date_        DATE

)

PARTITION BY RANGE (date_)

INTERVAL(NUMTOYMINTERVAL(1, ‘YEAR‘))

(

   PARTITION p1 VALUES LESS THAN (TO_DATE(‘4-6-2001’, ‘DD-MM-YYYY’)),

   PARTITION p2 VALUES LESS THAN (TO_DATE(‘5-5-2003’, ‘DD-MM-YYYY’)),

   PARTITION p3 VALUES LESS THAN (TO_DATE(‘6-4-2005’, ‘DD-MM-YYYY’))

); 

Table created.

When inserting rows with dates beyond the last defined range (greater than 06–04–2005), Oracle creates new interval partitions. The day and month of the new partition boundary match those of the last range partition:

SQL> insert into tbl values(7,’test’,TO_DATE(‘9-8-2041’, ‘DD-MM-YYYY’));

1 row inserted

SQL> insert into tbl values(7,’test’,TO_DATE(’10-11-2091′, ‘DD-MM-YYYY’));

1 row inserted

SQL> select PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       HIGH_VALUE

——————– ———–

P1                    2001-06-04

P2                    2003-05-05

P3                    2005-04-06

SYS_P501              2042-04-06

SYS_P521              2092-04-06

As shown, each newly created partition uses the same month and day (04–06) as the last range partition.


To change this behavior for future interval partitions:

  1. Disable interval partitioning:
SQL>  alter table tbl set interval();

Table altered

2. Add a new range partition with the desired date:

SQL> alter table tbl add partition p100 values less than (TO_DATE(‘3030-01-01‘,’YYYY-MM-DD’));

Table altered

3.Re-enable interval partitioning:

SQL> alter table tbl set interval(NUMTOYMINTERVAL(1,’YEAR‘));

Table altered

Now, new interval partitions will be created using the new boundary:

SQL> insert into tbl values(855,’test’,TO_DATE(’12-12-4040′, ‘DD-MM-YYYY’));

1 row inserted

SQL> select PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       HIGH_VALUE

——————– ———–

P1                   2001-06-04

P2                   2003-05-05

P3                   2005-04-06

SYS_P501             2042-04-06

SYS_P521             2092-04-06

P100                 3030-01-01

SYS_P541             4041-01-01

7 rows selected

Point 3: Naming Partitions

When adding a range partition, you can explicitly assign a name:

SQL>   alter table tbl add partition p2040 values less than (TO_DATE(‘4-6-2040’, ‘DD-MM-YYYY’));

Table altered

If you do not specify a name, Oracle assigns one automatically:

SQL>   alter table tbl add partition  values less than (TO_DATE(‘4-6-2040’, ‘DD-MM-YYYY’));

Table altered

SQL> select PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       HIGH_VALUE

——————–                   ——————–

P1                                           2001-06-04

SYS_P581                             2040-06-04

However, when interval partitioning is enabled, you cannot manually add partitions using this method:

SQL> alter table tbl add partition p4020 values less than (4);

ORA-14760: ADD PARTITION is not permitted on Interval partitioned objects

Starting with Oracle 12c Release 2, you can rename automatically created interval partitions:

SQL> select PARTITION_NAME,HIGH_VALUE,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       HIGH_VALUE INTERVAL

——————– ———- ——–

P1                   1          NO

SYS_P601             3          YES

SQL> alter table tbl rename partition for (2) to p3;

Table altered

SQL> select PARTITION_NAME,HIGH_VALUE,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       HIGH_VALUE INTERVAL

——————– ———- ——–

P1                   1          NO

P3                   3          YES

Point 4: Manually Adding Partitions to a Range-Interval Table

You cannot manually add partitions to a range-interval partitioned table using the standard method (without performing DML). However, this can be achieved using one of the following approaches.

Method 1: Using LOCK TABLE ... PARTITION FOR

SQL> select PARTITION_NAME,HIGH_VALUE,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME  HIGH_VALUE            INTERVAL

————— ———–          ——–

P1               2001-06-04         NO

SQL> LOCK TABLE tbl PARTITION FOR(TO_DATE(’11-12-2021′, ‘DD-MM-YYYY’)) IN SHARE MODE;

Table(s) locked

SQL> commit;

Commit complete

SQL> select PARTITION_NAME,HIGH_VALUE,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME  HIGH_VALUE  INTERVAL

————— ———– ——–

P1              2001-06-04   NO

SYS_P664 2022-06-04   YES

A common use case for this method is performing a partition exchange:

SQL> create table tbl_non_partition as select * from tbl where 1=2;

Table created

SQL> insert into tbl_non_partition values(855,’test’,TO_DATE(’09-09-2021′, ‘DD-MM-YYYY’));

1 row inserted

SQL> insert into tbl_non_partition values(855,’test’,TO_DATE(’07-07-2021′, ‘DD-MM-YYYY’));

1 row inserted

SQL> insert into tbl_non_partition values(855,’test’,TO_DATE(’08-08-2021′, ‘DD-MM-YYYY’));

1 row inserted

SQL> insert into tbl_non_partition values(855,’test’,TO_DATE(’10-10-2021′, ‘DD-MM-YYYY’));

1 row inserted

SQL> commit;

Commit complete

SQL> ALTER TABLE tbl

    EXCHANGE PARTITION FOR (TO_DATE(’11-12-2021′, ‘DD-MM-YYYY’))

      WITH TABLE tbl_non_partition;

Table altered

SQL> select count(*) from tbl_non_partition;

  COUNT(*)

———-

         0

SQL> select count(*) from tbl PARTITION FOR (TO_DATE(’11-12-2021′, ‘DD-MM-YYYY’));

  COUNT(*)

———-

         4

Method 2: temporary disabling interval partitioning and adding manually partitions:

 SQL> ALTER TABLE tbl SET INTERVAL ();

Table altered

SQL> alter table tbl add partition p4020 values less than (TO_DATE(’11-12-2021′, ‘DD-MM-YYYY’)) ;

Table altered

SQL> select PARTITION_NAME,HIGH_VALUE,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME  HIGH_VALUE  INTERVAL

————— ———-  ——–

P1              2001-06-04  NO

P4020           2021-12-11  NO

Point 5: Converting Range Partitioning to Interval Partitioning

Consider the following range-partitioned table:

create table tbl1 (

     id number,

     name varchar2(10),

     date_        DATE

)

PARTITION BY RANGE (date_)

(

   PARTITION p1 VALUES LESS THAN (TO_DATE(‘4-1-2001’, ‘DD-MM-YYYY’))

);

Attempting to insert data for the year 2030 results in an error:

SQL> insert into tbl1 values(6,’test’,TO_DATE(‘4-1-2030’, ‘DD-MM-YYYY’));

ORA-14400: inserted partition key does not map to any partition

By converting the table to interval partitioning, Oracle automatically creates new partitions for out-of-range values:

SQL> ALTER TABLE tbl1 SET INTERVAL(NUMTOYMINTERVAL(2,’YEAR’));

Table altered.

SQL> select partition_name, interval from   user_tab_partitions where  table_name = ‘TBL1’;

PARTITION_ INT

———- —

P1         NO

SQL> insert into tbl1 values(6,’test’,TO_DATE(‘4-1-2030’, ‘DD-MM-YYYY’));

1 row created.

SQL> select partition_name, interval from   user_tab_partitions where  table_name = ‘TBL1’;

PARTITION_ INT

———- —

P1         NO

SYS_P421   YES

Point 6: Converting Interval Partitioning to Range Partitioning

As shown, converting from range to interval automatically creates partition SYS_P421. To revert the table from interval to range partitioning:

SQL> ALTER TABLE tbl1 SET INTERVAL ();

Table altered.

SQL> select partition_name, interval from user_tab_partitions where  table_name = ‘TBL1’;

PARTITION_ INT

———- —

P1         NO

SYS_P421   NO

SQL>  insert into tbl1 values(7,’test’,TO_DATE(‘4-1-2040’, ‘DD-MM-YYYY’));

ORA-14400: inserted partition key does not map to any partition

Point 7: Distributing Interval Partitions Across Tablespaces

When using interval partitioning, partitions can be distributed across multiple tablespaces in a round-robin fashion using the STORE IN clause:

SQL> alter table tbl1 set STORE IN(tbs1,tbs2,tbs3,tbs4);

Table altered.

Comments

Post a Comment

Popular posts from this blog

Oracle 21c Enhancements for TTS Export/Import

By -
  In Oracle version 21c, new features have been introduced for transportable tablespace (TTS) export/import. This article explores these capabilities in detail. Improvement 1: Parallel Execution of TTS Export/Import In Oracle 19c, TTS export/import operations could not be executed in parallel, as demonstrated by the following: Connected to: Oracle Database 19c Enterprise Edition Release 19.0 .0 .0 .0 – Production ORA-39002: invalid operation ORA-39047: Jobs of type TRANSPORTABLE cannot use multiple execution streams. Oracle addressed this limitation in version 21c, enabling parallel execution for TTS export/import: [oracle@oLinux7 ~]$ expdp directory=drm dumpfile=TTS_DUMP%U.dmp transport_tablespaces=tbs1,tbs2 transport_full_check= y parallel= 2 Export: Release 21.0 . 0 . 0 . 0 – Production on Tue Oct 12 06 : 48 : 54 2021 Version 21.3 . 0 . 0 . 0 Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPOR...
Read more

Oracle 23ai — Speed up IMPDP Using NOVALIDATE Constraints

By -
  When executing the   IMPDP   command, some operations do not support parallel execution. Creating constraints is one such operation. Oracle must validate the current data in the table before creating a constraint, which requires a full table scan for each validation. This process can be very time-consuming. Consider the following constraints: ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK1 CHECK (col3 > 50 ); ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK2 CHECK (col9 IN ( 5 , 15 )); We drop the  MYTB  table and recreate it using a dump file: SQL > drop table mytb; Table dropped. W -1 Processing object type TABLE_EXPORT / TABLE / TABLE_DATA W -1 . . imported "VAHID"."MYTB" 19 GB 337830912 rows in 83 seconds using external_table W -1 Processing object type TABLE_EXPORT / TABLE / CONSTRAINT / CONSTRAINT W -1 Completed 2 CONSTRAINT objects in 208 seconds As shown, creating the two constraints ta...
Read more

Staging Tables in Oracle 23ai

By -
  As you may know, using   staging tables   is a common practice in   Data Warehouse   environments, primarily during the data loading process into the   Warehouse Database . Typically, raw data received from   source databases   is first loaded into a temporary area, referred to as   staging tables . After cleansing inconsistent and invalid data and transforming it into a standard format, the cleaned data is stored in the final operational tables. In addition to initial data loading,   staging tables   are also used for updates and are often truncated before reuse. In   Oracle 23ai , a new minor feature has been introduced for this purpose, allowing users to create tables specifically designed as   staging tables . This is achieved by adding the   FOR STAGING   clause to the   CREATE TABLE   statement. Creating Staging Tables Using the   FOR STAGING   clause, staging tables can be created as ...
Read more