Observations on an Oracle bug: You can't CTAS an IOT with Timestamp(n)!

If you are planning on loading data into a partitioned IOT with a "Timestamp(n)" field using CTAS, well, you can't!

I came across the issue a while ago, and had it confirmed by Oracle as a bug, which manifests itself even in 12cR1.

Here is the scenario:

You would require to load an IOT (named EMP) by adding new partitions and exchanging with another IOT that's produced using a CTAS.

This is basically what you would need to do:

CREATE TABLE EMP(
DeptID NUMBER,
EmpID   NUMBER,
HireDate TIMESTAMP(3),
CONSTRAINT pk_emp PRIMARY KEY(DeptID, EmpID))
 ORGANIZATION INDEX COMPRESS 1
 PARTITION BY LIST(DeptID)
 (PARTITION DEPT_0 VALUES(0));

CREATE TABLE Temp(
DeptID,
EmpID,
HireDate,
CONSTRAINT pk_temp PRIMARY KEY(DeptID, EmpID)) ORGANIZATION INDEX COMPRESS 1 AS
SELECT 1,1,CAST(SYSTIMESTAMP AS TIMESTAMP(3))
FROM dual;

ALTER TABLE Emp ADD PARTITION DEPT_1 VALUES(1);

ALTER TABLE Emp EXCHANGE PARTITION DEPT_1 WITH TABLE Temp;

And That's when you're hit in the face by a:

SQL Error: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
14097. 00000 -  "column type or size mismatch in ALTER TABLE EXCHANGE PARTITION"
*Cause:    The corresponding columns in the tables specified in the
           ALTER TABLE EXCHANGE PARTITION are of different type or size
*Action:   Ensure that the two tables have the same number of columns
           with the same type and size.

Obviously you will check:

desc emp
Name     Null     Type      
-------- -------- ------------
DEPTID   NOT NULL NUMBER    
EMPID    NOT NULL NUMBER    
HIREDATE          TIMESTAMP(3)

desc temp
Name     Null     Type      
-------- -------- ------------
DEPTID   NOT NULL NUMBER    
EMPID    NOT NULL NUMBER    
HIREDATE          TIMESTAMP(3)

To prove that it is actually possible to exchange partition with the IOT:

CREATE TABLE Temp1(
DeptID NUMBER,
EmpID   NUMBER,
HireDate TIMESTAMP(3),
CONSTRAINT pk_temp1 PRIMARY KEY(DeptID, EmpID))
 ORGANIZATION INDEX COMPRESS 1;

INSERT INTO Temp1 VALUES(1,2,SYSTIMESTAMP);
COMMIT;

ALTER TABLE Emp EXCHANGE PARTITION DEPT_1 WITH TABLE Temp1;

And you're politely made aware of the fact that:

"table EMP altered."

What's going on here?

Well, the following simple query would shine enough light on the issue:

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_ID, DATA_TYPE, DATA_LENGTH, DATA_SCALE
FROM USER_TAB_COLS
WHERE TABLE_NAME IN ('EMP','TEMP','TEMP1')
ORDER BY 3,1;

TABLE_NAME COLUMN_NAME  COLUMN_ID DATA_TYPE    DATA_LENGTH
---------- ----------- ---------- ------------ -----------
EMP        DEPTID               1 NUMBER                22
TEMP       DEPTID               1 NUMBER                22
TEMP1      DEPTID               1 NUMBER                22
EMP        EMPID                2 NUMBER                22
TEMP       EMPID                2 NUMBER                22
TEMP1      EMPID                2 NUMBER                22
EMP        HIREDATE             3 TIMESTAMP(3)          11
TEMP       HIREDATE             3 TIMESTAMP(3)          20
TEMP1      HIREDATE             3 TIMESTAMP(3)          11 

As you can see CAST(column as TIMESTAMP(n)) would merely manifest itself as a TIMESTAMP(n) but in fact, it's not different than a "TIMESTAMP!"

Solution:

Modify the source of your CTAS operation to actually hold a "TIMESTAMP(n)" value, and get rid of the CAST function. Although the function works for other data types.

Note to self: How (not) to exchange partitions

Disclaimer: This is a "note to self;" not a guide, and by no means anything close to a "complete" guide! These are just the points that I can remember RIGHT NOW! :)

I spent a complete day on diagnosing a failing exchange subpartition operation; hence this post.

I was particularly disappointed (because of the OakTable accreditations) when I noticed that "Expert Oracle Practices" (Apress - OakTable, p353) describes the required conditions for a successful partition exchange in only one -occasionally vague- sentence: "[both segments should] have the same logical shape (that is, the same columns, data types, scalability[DATA_SCALE I assume], precision, data- integrity constraints such as NULL/NOT-NULL, and so forth)" although it has to be said that this book has got far more content than any other books I've recently read, and specifically details the workflow for exchanging partitions, in page 356, under "Loading Millions of Rows."

Pro Oracle 11g Administration sheds a little bit more light (p295) by saying: "[both segments] must have the same primary-key structure defined."

Oracle Database 11g Handbook suffices to say that (p266) both segments should have "the same column structure" and has reminder that in terms of the work flow you should:

1. Load data into the stand alone table
2. Analyze the table
3. Build the indexes "to match the partitioned table’s indexes" but doesn't elaborate on what does this "matching" entails. It also adds: "the indexes [on the partitioned table, obv!] must be local, and not global, indexes."

Based on my experience (11.2.0.3) you actually CAN exchange partitions without gathering stats on the stand alone table, but I'm not sure how it would benefit anyone, mainly because it can lead to stat anomalies on partitioned table, and severely affect the execution plan; unless -for whatever reason and through whatever means- you've made sure that you're not gathering any kind of stats on the partitioned table.

Also the indexes on both sides do not need to have the same name; all that matters is that both sides have the same number of indexes, and the indexes are matching in terms of type (Bitmap, B-Tree) and the columns that the indexes are built on.

Two other points on the indexes:

1. If you are using Bitmap indexes you HAVE TO make sure the Hakan Factor on both segments involved in the exchange are the same. (details: Metalink 1389444.1, although I'm more in favour of doing an "ALTER TABLE xxx NOMINIMIZE RECORDS_PER_BLOCK" on both tables when they're empty.)
2. if there are indexes on virtual columns, the definition of the virtual columns should match as well.

On the nature of columns:

Apart from name, type, and size/precision of columns on both sides, the order of the columns should match too. The presence of virtual columns in between non-virtual ones will not cause failure in partition exchange as long as the order of non-virtual columns are the same.

Notes:

• I have not tested exchanging partitions that contain Spatial indexes.
• This post will be updated, as soon as I remember/come across other points
• Or if you'll kindly let me know, if you know of other missing bits of info :)