partition-wise dependencies in 10g release 2

For several releases of Oracle we have been able to perform most partition-level DDL operations without invalidating dependant objects such as packages and views. Supported operations include partition-level truncates, moves, splits, merges, exchanges and additions. An important omission from this list is the ability to drop partitions without invalidating dependant objects. Oracle has added support for this to 10g Release 2 (10.2) and this short article describes this new feature.

why is this important?

Many systems use partitioning for performance, maintenance, ease of loading, availability and so on. One of the common uses of range-partitioned tables, for example, is in "rolling" or "sliding" window applications. By this we mean that data is retained in these tables for a finite period of time and as new partitions are added to the table, the oldest partitions are removed. Partition-drop is the quickest way of purging old data from a table, but prior to 10g Release 2, dropping a partition invalidates all dependant objects. The importance of this new feature, therefore, is that it gives further support and resilience to sliding window applications that have been in place since range partitioning was introduced in Oracle 8.0.

setup

To demonstrate, we will create three simple objects: a partitioned table, a dependant procedure and a dependant view. The table is list-partitioned, for no reason other than the syntax is more simple, and is created as follows.

SQL> CREATE TABLE partitioned_table
  2  ( x INT
  3  , y INT )
  4  PARTITION BY LIST (y)
  5  ( PARTITION p1 VALUES (10)
  6  , PARTITION p2 VALUES (20)
  7  , PARTITION p3 VALUES (30) );

Table created.

We will create a procedure and view with hard dependencies on the partitioned table as follows.

SQL> CREATE PROCEDURE dependant_procedure AS
  2     v_cnt PLS_INTEGER;
  3  BEGIN
  4     SELECT COUNT(*) INTO v_cnt
  5     FROM   partitioned_table;
  6  END dependant_procedure;
  7  /

Procedure created.

SQL> CREATE VIEW dependant_view
  2  AS
  3     SELECT *
  4     FROM   partitioned_table;

View created.

Before we demonstrate the new dependency feature (i.e. no invalidation with partition drops), we will check the current status of our dependant objects as follows.

SQL> SELECT object_name, status
  2  FROM   user_objects
  3  WHERE  object_name LIKE 'DEPENDANT%';

OBJECT_NAME               STATUS
------------------------- -------
DEPENDANT_PROCEDURE       VALID
DEPENDANT_VIEW            VALID

2 rows selected.

drop partition and dependant objects

We will now drop one of the partitions from our table, as follows.

SQL> ALTER TABLE partitioned_table DROP PARTITION p1;

Table altered.

In versions prior to 10.2, this operation would invalidate all dependant objects. In 10.2, we see the following.

SQL> SELECT object_name, status
  2  FROM   user_objects
  3  WHERE  object_name LIKE 'DEPENDANT%';

OBJECT_NAME               STATUS
------------------------- -------
DEPENDANT_PROCEDURE       VALID
DEPENDANT_VIEW            VALID

2 rows selected.

So far we have tested on an empty table. For completeness, we will populate a remaining partition with some data, drop the partition and examine the status of the dependant objects. First we will add some data as follows.

SQL> INSERT INTO partitioned_table
  2  SELECT ROWNUM, 20
  3  FROM   dual
  4  CONNECT BY ROWNUM <= 100;

100 rows created.

SQL> COMMIT;

Commit complete.

We will now drop the partition we populated and check the status of our dependant objects.

SQL> ALTER TABLE partitioned_table DROP PARTITION p2;

Table altered.

SQL> SELECT object_name, status
  2  FROM   user_objects
  3  WHERE  object_name LIKE 'DEPENDANT%';

OBJECT_NAME               STATUS
------------------------- -------
DEPENDANT_PROCEDURE       VALID
DEPENDANT_VIEW            VALID

2 rows selected.

This has made no difference in this case. Some readers might be surprised that we bothered with such a test but, in some operations, Oracle will treat a table with a history of data differently to a table that has never had data (we will see this later in this article).

dependencies on specific partitions

In the previous examples, all dependencies were on the partitioned table and not on any partitions in particular. Now that we have established the fact that we can remove partitions from tables without invalidating our dependant objects, we will examine the effects on objects that reference specific partitions. First we will refresh our sample objects, as follows.

SQL> DROP TABLE partitioned_table PURGE;

Table dropped.

SQL> DROP PROCEDURE dependant_procedure;

Procedure dropped.

SQL> DROP VIEW dependant_view;

View dropped.

We will re-create the partitioned table we used earlier.

SQL> CREATE TABLE partitioned_table
  2  ( x INT
  3  , y INT )
  4  PARTITION BY LIST (y)
  5  ( PARTITION p1 VALUES (10)
  6  , PARTITION p2 VALUES (20)
  7  , PARTITION p3 VALUES (30) );

Table created.

We will create the dependant procedure and view as follows, but this time they will both be explicitly dependant on partition P1.

SQL> CREATE PROCEDURE dependant_procedure AS
  2     v_cnt PLS_INTEGER;
  3  BEGIN
  4     SELECT COUNT(*) INTO v_cnt
  5     FROM   partitioned_table PARTITION (p1);
  6  END dependant_procedure;
  7  /

Procedure created.

SQL> CREATE VIEW dependant_view
  2  AS
  3     SELECT *
  4     FROM   partitioned_table PARTITION (p1);

View created.

To repeat the tests from earlier, we will now drop partition P1 and check the status of our dependant objects.

SQL> ALTER TABLE partitioned_table DROP PARTITION p1;

Table altered.

SQL> SELECT object_name, status
  2  FROM   user_objects
  3  WHERE  object_name LIKE 'DEPENDANT%';

OBJECT_NAME               STATUS
------------------------- -------
DEPENDANT_PROCEDURE       VALID
DEPENDANT_VIEW            VALID

2 rows selected.

It appears, therefore, that Oracle makes a blanket assumption that dropping a partition should not invalidate any dependant objects, regardless of whether the partition in question is explicitly referenced. If we try to execute the dependant procedure, we see the following.

SQL> BEGIN
  2     dependant_procedure;
  3  END;
  4  /

BEGIN
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
ORA-06512: at "SCOTT.DEPENDANT_PROCEDURE", line 4
ORA-06512: at line 2

This is to be expected: the partition doesn't exist because we dropped it! The same exception is raised if we try to select from the dependant view, as follows.

SQL> SELECT *
  2  FROM   dependant_view;

FROM   dependant_view
       *
ERROR at line 2:
ORA-02149: Specified partition does not exist

Some care should be taken when relying on this new feature. In general, however, it will be very useful for applications that require a sliding window of partitions (i.e. add-new, drop-old).

partition drop and referential integrity

Now that Oracle supports partition drop, it seems that the last element of support required (especially in sliding window applications) relates to referential integrity. For example, we might have a parent-child relationship between two partitioned tables and need to support the dropping of the oldest partitions from both tables. Unfortunately, this is not supported in 10.2 without constraint disabling (which in highly concurrent batches or systems might not be possible).

We will see an example of what the preceding paragraph relates to below. First we will create two related partitioned tables with an equivalent partitioning scheme as follows.

SQL> CREATE TABLE parent_partitioned_table
  2  ( x INT
  3  , y INT
  4  , z INT
  5  , CONSTRAINT ppt_pk
  6       PRIMARY KEY (x,y)
  7  )
  8  PARTITION BY LIST (y)
  9  ( PARTITION p1 VALUES (10)
 10  , PARTITION p2 VALUES (20)
 11  , PARTITION p3 VALUES (30) );

Table created.

SQL> CREATE TABLE child_partitioned_table
  2  ( x  INT
  3  , y  INT
  4  , px INT
  5  , CONSTRAINT cpt_pk
  6       PRIMARY KEY (x,y)
  7  , CONSTRAINT cpt_fk
  8       FOREIGN KEY (px,y)
  9       REFERENCES parent_partitioned_table (x,y)
 10  )
 11  PARTITION BY LIST (y)
 12  ( PARTITION p1 VALUES (10)
 13  , PARTITION p2 VALUES (20)
 14  , PARTITION p3 VALUES (30) );

Table created.

Remember earlier that we included an additional test for populated partitions and commented that some operations can behave differently once a table has been populated. The following is one such example. Neither the parent nor the child partitioned table have been populated and we are able to drop a partition from the parent table, as follows.

SQL> ALTER TABLE parent_partitioned_table DROP PARTITION p1;

Table altered.

Ignoring the fact that we have left a partition in the child table that can never accept data from this point forwards, we will now populate a matching partition in both tables, as follows.

SQL> INSERT INTO parent_partitioned_table VALUES (1, 20, 1);

1 row created.

SQL> INSERT INTO child_partitioned_table
  2  SELECT ROWNUM, 20, 1
  3  FROM   dual
  4  CONNECT BY ROWNUM <= 100;

100 rows created.

SQL> COMMIT;

Commit complete.

As we might expect, we cannot drop the parent partition because it contains data referenced by the child partition.

SQL> ALTER TABLE parent_partitioned_table DROP PARTITION p2;

ALTER TABLE parent_partitioned_table DROP PARTITION p2
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

We will therefore drop the child partition as follows.

SQL> ALTER TABLE child_partitioned_table DROP PARTITION p2;

Table altered.

Now that we have dropped the child partition, the parent partition's data is no longer referenced. Despite this, there is currently no support in 10.2 for a parent-partition drop, as we see below.

SQL> ALTER TABLE parent_partitioned_table DROP PARTITION p2;

ALTER TABLE parent_partitioned_table DROP PARTITION p2
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

While not strictly related to the main subject of this article, this restriction still impacts our ability to build sliding window applications for partitioned tables, particularly where constraints cannot be disabled.

source code

The source code for the examples in this article can be downloaded from here.

Adrian Billington, October 2007 (updated August 2008)

oracle-developer.net