Partition Exchange in Oracle 12.2

There are many new extensions for Partitioning in Oracle 12c Release 2 (12.2). One of them is the CREATE TABLE FOR EXCHANGE statement. Some years ago, I wished to have this feature for a particular problem with hidden columns.

Column mismatch with hidden columns

Several years ago, I worked in a data warehouse project on an Oracle 11.2 database. We intensely used Partition Exchange for all our load jobs. The exchange tables were automatically created with a CREATE TABLE AS SELECT (CTAS) statement like this:

CREATE TABLE tmp_sales AS
SELECT * FROM sales
WHERE 1 = 2

One day, the job failed for one of the partitioned tables during the Partition Exchange statement, although is seemed to be obvious that the table structure of the exchange table and the partitioned target table are identical. The following error message appeared:

ALTER TABLE sales
EXCHANGE PARTITION p_2012_06
WITH TABLE tmp_sales
INCLUDING INDEXES WITHOUT VALIDATION

ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

I remember that I spent a lot of time to find the difference. Finally, I found a hidden column in the partitioned table. One of the developers had to remove a fact column due to a change request. To save time, he did not drop the column, but set it to unusable. Of course, the column was not called “alternative_fact” – this is only an example to show the principle.

ALTER TABLE sales SET UNUSED COLUMN alternative_fact

An unusable column is internally renamed by Oracle and set to “hidden”, but is still stored in the segment. The data dictionary view USER_TAB_COLUMNS does not show the hidden columns, but USER_TAB_COLS tells the truth:

SELECT column_id, column_name, hidden_column
FROM user_tab_cols
WHERE table_name = 'SALES'
ORDER BY internal_column_id

COLUMN_NAME                HIDDEN_COLUMN
---------------------------------------------------- --------------------------
SHOP_ID                    NO 
PROD_ID                    NO 
CUST_ID                    NO 
TIME_ID                    NO 
QTY_SOLD                   NO 
SYS_C00006_17012402:02:42$ YES
AMT_SOLD                   NO

The CTAS statement above copies all regular columns, but not the hidden ones. So, it was clear that the internal structure of the exchange table and the table partition were different.

Because this may happen again – there were many developers in this project, and forbid to use hidden columns was not an option in this case – I had to implement a more complex solution. Instead of just copying the table structure with  a CTAS command, I implemented a PL/SQL procedure that creates the exchange table with the information available in USER_TAB_COLS, including unusable and virtual columns (invisible columns were not yet available in Oracle 11g). It worked, but the solution was not very elegant.

Creating exchange tables in Oracle 12.2

The problem described above can be solved now much easier with one of the new partition features of Oracle Database 12.2. An enhancement of the CREATE TABLE command allows to create an empty exchange table with the same structure as the source table in one step:

CREATE TABLE tmp_sales 
FOR EXCHANGE WITH TABLE sales

The Oracle SQL Reference Manual describes the functionality of this feature with the following explanation:

This operation creates a metadata clone, without data, of the partitioned table. The clone has the same column ordering and column properties of the original table. Column properties copied to the clone during this operation include unusable columns, invisible columns, virtual expression columns, functional index expression columns, and other internal settings and attributes. Indexes on the existing partitioned table are not created on the clone table.

Perfect! Exactly what I need (unfortunately a few years to late, but similar situations may happen in future projects as well). The only restriction is that indexes are not automatically created. First I thought this is a drawback, but actually this makes sense. The recommended steps are:

1. Create an empty exchange table. This can be done with the new 12.2 command.
2. Load the data into the exchange table with Direct-Path INSERT. Because of Online Statistics Gathering, the table and column statistics on the exchange table are calculated automatically.
3. Create the same indexes as the (local) indexes on the partitioned target table. It is better to create the indexes after loading the data, not on the empty table. And by the way, the index statistics are gathered during index creation automatically.
4. Finally, exchange the contents of the exchange table with the corresponding partition of the target table. With the options INCLUDING INDEXES and WITHOUT VALIDATION, this step is very fast and works in the same way as in previous Oracle releases – but now without an ORA-14097 error.

ALTER TABLE sales
EXCHANGE PARTITION p_2017_02
WITH TABLE tmp_sales
INCLUDING INDEXES WITHOUT VALIDATION

The CREATE TABLE FOR EXCHANGE command is one of the useful extensions for Partitioning in Oracle Database 12c Release 2. I’m looking forward to work with the new database release in my current or future data warehouse projects. More features of Oracle 12.2 – not only about Partitioning – are explained in the TechnoCircle Oracle Database 12c Release 2 New Features – coming soon at Trivadis Training.