Wednesday 19 March 2014

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.

Sunday 2 February 2014

The challenging journey to overcome lack of knowledge

It should be called a crime when people liken the process of "gaining knowledge" to simply "solving a puzzle."

At least we all have to agree that it's an extremely patronising way of describing the process; mainly because the person in the position of power (in this case "knowledge"), the same person who's describing the process of "gaining knowledge," fails to address the characteristics of that "puzzle" honestly or correctly.

Knowledge has no shape

This is for people like me, who aspire to be good at what they do; those who have a dream to be true professional -no matter whether they put in enough effort or not- and they are very well aware of the fact that "getting better" is directly associated with gaining more knowledge.

This specifically holds for people who aspire to carry the title of "Oracle Scientist."

The fact of the matter is: knowledge is a puzzle; but with no shape or specific boundaries.

You keep discovering new pieces but you have no idea where -in the broad context of this shapeless puzzle- the pieces sit.

And in the case of Oracle, most of the time the pieces can sit right in the middle of the puzzle!

Embrace the pilgrimage!

I'd have to apologise for committing "the crime" too; because the process of gaining knowledge is no journey.

It's a pilgrimage.

You would know it's a pilgrimage when you overcome the initial fear that takes over you, when you realise that the "puzzle piece" you just found can ONLY fit in the middle, not directly connected to any other pieces you've previously found, but can be potentially connected to either of them indirectly!

Easy answers are rare, the path is long, and above all -as Connor McDonald rightfully quotes- "Learning is not a spectator sport!"

Find a guiding light!

Nobody has done it on his own, EVER.

I've been reminded of this, time and time again during the course of my life, and most lately by Kirby Ferguson.

You need to be surrounded by knowledgable people; the kind of people who don't give you the answer, but are patient enough with you so that you'd learn the path to acquire that answer.

There's no shortage of such people in the Oracle community, if you've got the will to find them.