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:
- 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.
- If the partitioned table contains local indexes, the corresponding indexes are created on the intermediate table.
- 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.
- 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
EXCHANGE PARTITION p_2012_04
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:
- 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.
- 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.
- 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:
-- select first row of stage table
SELECT * INTO v_row FROM stg_sales WHERE ROWNUM = 1;
-- insert dummy row to create missing partition
INSERT INTO fct_sales 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 fct_sales '
|| 'EXCHANGE PARTITION '||v_partname
|| ' WITH TABLE stg_sales'
|| ' INCLUDING INDEXES WITHOUT VALIDATION';
EXECUTE IMMEDIATE v_sql;
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.