Partition Exchange and Interval Partitioning

Partition Exchange and Interval Partitioning

Rate This
  • Comments 2

Last week I got an interesting question from a former colleague: How can interval partitioning in Oracle 11g be combined with partition exchange?

With interval partitioning, it is not possible to create a new partition manually. Usually, this is what we want when we use interval partitioning. Oracle checks for each new row whether the corresponding partition already exists. If a new partition is required, it is created automatically. As long as we insert new rows into the partitioned table directly, this behavior is perfect. But what happens if we use partition exchange?

Concept of Partition Exchange

The typical approach with partition exchange contains several steps:

  1. Data is loaded into an intermediate table (or a "stage table" in a DWH environment). This table has exactly the same structure as the partitioned table and contains all rows of a particular load, e.g. all fact data of one day or one month.
  2. If the partitioned table contains local indexes, the corresponding indexes are created on the intermediate table.
  3. A new partition is created on the partitioned table. For local indexes, the associated index partitions are created implicitly. After this step, the new partition is empty.
  4. Now, a partition exchange between the intermediate table and the new partition is performed. Technically, only two pointers in the data dictionary are swapped: The intermediate table becomes the partition, and the partition is now the intermediate table. After this step, the new partition contains the data of the intermediate table - and vice versa.

For example, we load all sales data of April 2012 into a stage table STG_SALES and do a partition exchange with the April partition of the fact table FCT_SALES:

ALTER TABLE fct_sales
WITH TABLE stg_sales

Partition Exchange on Interval Partitioned Tables

But how does this work with interval partitioning? There are two issues: First, we cannot create a new partition explicitly. Second, we don't know the name of the partition because partition names are generated automatically (e.g. SYS_P22).

My answer to the question of my colleague was to use the following steps:

  1. Insert a dummy row into the interval partitioned  table. This dummy row can be the first row of the stage table. The only purpose of this step is that a new partition is created.
  2. Find out the name of the new partition. This sounds simple, but is actually the hardest part of this solution. We could retrieve the partition with the newest creation data or the highest position number, but this is probably not sufficient in all situations.
  3. Now, a partition exchange between the determined partition and the stage table is performed. After this step, the complete data set is stored in the new partition, and the stage table contains exactly one row - the dummy row.

My colleague was happy with this answer. But I was still looking for a general solution to find out the right partition name without any restrictions of load order or creation date.

The following PL/SQL block can be used to solve this problem. It selects one row from the stage table and inserts it into the interval partitioned table. An INSERT ... VALUES statement is used for this step because this allows to return the ROWID of the inserted row in PL/SQL. With the predefined package DBMS_ROWID and the data dictionary view USER_OBJECTS the partition name can be derived from the ROWID. As a last step, the partition exchange is executed with dynamic SQL:

   v_row      stage_table%ROWTYPE;
   v_rowid    ROWID;
   v_partname VARCHAR2(30);
   v_sql      VARCHAR2(1000);
   -- select first row of stage table
   SELECT * INTO v_row FROM stage_table WHERE ROWNUM = 1;
   -- insert dummy row to create missing partition
   INSERT INTO part_table VALUES v_row RETURNING ROWID INTO v_rowid;

-- get partition name from ROWID of dummy row
   SELECT subobject_name INTO v_partname FROM user_objects
    WHERE object_id = dbms_rowid.rowid_object(v_rowid);

-- partition exchange between stage table and new partition
   v_sql := 'ALTER TABLE part_table EXCHANGE PARTITION '||v_partname

Of course, this solution is not what I would use in a real project. A more elegant way would be a generic procedure where the names of the stage and target table are defined as input parameters. Although I implemented such things in several customer projects, I never did it for interval partitioned tables. The reason is simple: I never thought about combining partition exchange and interval partitioning. At least until last week when I received the question from my former colleague.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Post
  • I like the idea of interval partitioning but there are some issues to solve. I.e. if you want to drop a partition in a rolling window. There is a solution described teher:

  • Thanks for the post.

    Below must be data_object_id  instead of object_id:

      -- get partition name from ROWID of dummy row

      SELECT subobject_name INTO v_partname FROM user_objects

       WHERE data_object_id = dbms_rowid.rowid_object(v_rowid);

Page 1 of 1 (2 items)