I came across an interesting issue yesterday whereby a partition exchange load routine wasn’t working for my colleague Jon. The ensuing investigation seems to point at the possibility of a bug, whereby after exchanging a partition Oracle may have inadvertently transposed columns in the target table. I’ve created a test script to illustrate the problem and logged an SR with Metalink to see what they make of it.
In light of my recent post on DBMS_APPLICATION_INFO I’d better state that we found the problem on 10.2.0.2 on HP-UX and then tested it against 9.2.0.6 on HP-UX and found the same results so it seems the issue has been there a while in terms of Oracle releases.
The investigation started down a different avenue – as they often do – with the inability to complete a partition exchange due to the Oracle error:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
Reissuing the exchange partition command with the EXCLUDING INDEXES clause allowed the exchange to proceed so we figured there was definitely a problem with the indexes.
Next we tried dropping all the indexes on both sides of the exchange and recreating them…but when we retried the exchange partition it failed again with the same error.
Right, drastic measures time…we dropped all the indexes on both sides and ran the exchange which, with no indexes around, worked fine (with INCLUDING INDEXES clause in place as it would normally be on this process).
Next we proceeded to add the indexes one by one, starting with the b trees for the primary key and unique keys. This worked without a problem so we moved on to the bitmap indexes which seemed to be fine for the first few but then after adding a few of them it suddenly stopped working. Through a process of trial and error we were able to determine that if two specific bitmap indexes were on the tables then the exchange would fail with ORA-14098.
So, what was so special about those two bitmaps and the columns they were on. We selected the details from dba_tab_columns for the two columns involved in these bitmap indexes and realised that the only difference was that they were at a different column position (COLUMN_ID) on the tables – which begged the question “If these two tables are not quite the same then how can we be allowed to exchange them?”
Well, I guess, in our case, we were able to say that the two objects being exchanged had the same columns but just not necessarily in the same order (those of you who love classic comedy will I’m sure be recalling Morecambe and Wise with Andre Previn right about now)…should this mean we can swap the tables or not ?
To try and illustrate the scenario I built a test script…the output from a run follows…sorry it’s a bit long…but it does prove some useful conclusions (I think)
> @test_pel_bug_mismatch_columns
> REM Drop the tables…
> DROP TABLE jeff_test
/
Table dropped.
> DROP TABLE jeff_test_ptn
/
Table dropped.
> REM Create the tables…
> CREATE TABLE jeff_test_ptn(partition_key_col NUMBER NOT NULL
2 ,column1 NUMBER NOT NULL
3 ,column3 NUMBER NOT NULL
4 ,column2 NUMBER NOT NULL
5 ,column4 NUMBER NOT NULL
6 )
7 PARTITION BY RANGE (partition_key_col)
8 (
9 PARTITION p001 VALUES LESS THAN (1000001)
10 ,PARTITION p002 VALUES LESS THAN (MAXVALUE)
11 )
12 /
Table created.
> CREATE TABLE jeff_test(partition_key_col NUMBER NOT NULL
2 ,column1 NUMBER NOT NULL
3 ,column2 NUMBER NOT NULL
4 ,column3 NUMBER NOT NULL
5 ,column4 NUMBER NOT NULL
6 )
7 /
Table created.
> REM Populate the table…
> INSERT /*+ APPEND */
2 INTO jeff_test_ptn(partition_key_col
3 ,column1
4 ,column2
5 ,column3
6 ,column4
7 )
8 SELECT idx
9 , MOD(idx,2)
10 , MOD(idx,3)
11 , MOD(idx,4)
12 , MOD(idx,2)
13 FROM (SELECT LEVEL idx FROM dual CONNECT BY LEVEL
COMMIT
2 /
Commit complete.
> INSERT /*+ APPEND */
2 INTO jeff_test(partition_key_col
3 ,column1
4 ,column2
5 ,column3
6 ,column4
7 )
8 SELECT idx
9 , MOD(idx,2)
10 , MOD(idx,5)
11 , MOD(idx,6)
12 , MOD(idx,2)
13 FROM (SELECT LEVEL idx FROM dual CONNECT BY LEVEL
COMMIT
2 /
Commit complete.
> REM Count the rows in COLUMN2 and COLUMN3 for both tables…
>
> REM Should see 3 rows…all NUMBERS
> SELECT column2,COUNT(1) FROM jeff_test_ptn GROUP BY column2;
COLUMN2 COUNT(1)
———- ———-
1 12000
2 12000
0 12000
3 rows selected.
>
> REM Should see 4 rows…all NUMBERS
> SELECT column3,COUNT(1) FROM jeff_test_ptn GROUP BY column3;
COLUMN3 COUNT(1)
———- ———-
1 9000
2 9000
3 9000
0 9000
4 rows selected.
>
> REM Should see 5 rows…all NUMBERS
> SELECT column2,COUNT(1) FROM jeff_test GROUP BY column2;
COLUMN2 COUNT(1)
———- ———-
1 7200
2 7200
4 7200
3 7200
0 7200
5 rows selected.
>
> REM Should see 6 rows…all NUMBERS
> SELECT column3,COUNT(1) FROM jeff_test GROUP BY column3;
COLUMN3 COUNT(1)
———- ———-
1 6000
2 6000
4 6000
5 6000
3 6000
0 6000
6 rows selected.
>
> REM Now lets try and swap the partition and the table…
> ALTER TABLE jeff_test_ptn
2 EXCHANGE PARTITION p001
3 WITH TABLE jeff_test
4 INCLUDING INDEXES
5 WITHOUT VALIDATION
6 /
Table altered.
>
> REM Surprisingly, it lets us do the above operation without complaining.
> REM Even worse…it transposes the values in COLUMN2 and COLUMN3…
>
> REM Should see 5 rows…but we see 6 rows
> SELECT column2,COUNT(1) FROM jeff_test_ptn GROUP BY column2;
COLUMN2 COUNT(1)
———- ———-
1 6000
2 6000
4 6000
5 6000
3 6000
0 6000
6 rows selected.
>
> REM Should see 6 rows…but we see 5 rows
> SELECT column3,COUNT(1) FROM jeff_test_ptn GROUP BY column3;
COLUMN3 COUNT(1)
———- ———-
1 7200
2 7200
4 7200
3 7200
0 7200
5 rows selected.
>
> REM Should see 3 rows…but we see 4 rows
> SELECT column2,COUNT(1) FROM jeff_test GROUP BY column2;
COLUMN2 COUNT(1)
———- ———-
1 9000
2 9000
3 9000
0 9000
4 rows selected.
>
> REM Should see 4 rows…but we see 3 rows
> SELECT column3,COUNT(1) FROM jeff_test GROUP BY column3;
COLUMN3 COUNT(1)———- ———-
1 12000
2 12000
0 12000
3 rows selected.
>
> REM Now, lets try again but with COLUMN2 and COLUMN3
> REM being of different datatypes…
>
> REM Drop the tables…
> DROP TABLE jeff_test
2 /
Table dropped.
> DROP TABLE jeff_test_ptn
2 /
Table dropped.
> REM Create the tables…
> CREATE TABLE jeff_test_ptn(partition_key_col NUMBER NOT NULL
2 ,column1 NUMBER NOT NULL
3 ,column3 NUMBER NOT NULL
4 ,column2 DATE NOT NULL
5 ,column4 NUMBER NOT NULL
6 )
7 PARTITION BY RANGE (partition_key_col)
8 (
9 PARTITION p001 VALUES LESS THAN (1000001)
10 ,PARTITION p002 VALUES LESS THAN (MAXVALUE)
11 )
12 /
Table created.
> CREATE TABLE jeff_test(partition_key_col NUMBER NOT NULL
2 ,column1 NUMBER NOT NULL
3 ,column2 DATE NOT NULL
4 ,column3 NUMBER NOT NULL
5 ,column4 NUMBER NOT NULL
6 )
7 /
Table created.
> REM Populate the table…
> INSERT /*+ APPEND */
2 INTO jeff_test_ptn(partition_key_col
3 ,column1
4 ,column2
But it is important to try description now free sample of viagra note that Kamagra is not an aphrodisiac. They are cialis canadian prices greyandgrey.com considered as a liver and kidney tonic. Sildamax cialis 20 mg is prepared in the clinically clean and healthy conditions to provide safe solution for ED treatment. The most cautious consumers seek online stores that can offer you generic cialis professional medicines. 5 ,column3
6 ,column4
7 )
8 SELECT idx
9 , MOD(idx,2)
10 , SYSDATE + MOD(idx,3)
11 , MOD(idx,4)
12 , MOD(idx,2)
13 FROM (SELECT LEVEL idx FROM dual CONNECT BY LEVEL
COMMIT
2 /
Commit complete.
> INSERT /*+ APPEND */
2 INTO jeff_test(partition_key_col
3 ,column1
4 ,column2
5 ,column3
6 ,column4
7 )
8 SELECT idx
9 , MOD(idx,2)
10 , SYSDATE + MOD(idx,5)
11 , MOD(idx,6)
12 , MOD(idx,2)
13 FROM (SELECT LEVEL idx FROM dual CONNECT BY LEVEL
COMMIT
2 /
Commit complete.
> REM Count the rows in COLUMN2 and COLUMN3 for both tables…
>
> REM Should see 3 rows…all DATES
> SELECT column2,COUNT(1) FROM jeff_test_ptn GROUP BY column2;
COLUMN2 COUNT(1)
——————– ———-
27-SEP-2006 11:49:03 12000
28-SEP-2006 11:49:03 12000
26-SEP-2006 11:49:03 12000
3 rows selected.
>
> REM Should see 4 rows…all NUMBERS
> SELECT column3,COUNT(1) FROM jeff_test_ptn GROUP BY column3;
COLUMN3 COUNT(1)
———- ———-
1 9000
2 9000
3 9000
0 9000
4 rows selected.
>
> REM Should see 5 rows…all DATES
> SELECT column2,COUNT(1) FROM jeff_test GROUP BY column2;
COLUMN2 COUNT(1)
——————– ———-
30-SEP-2006 11:49:03 7200
27-SEP-2006 11:49:03 7200
28-SEP-2006 11:49:03 7200
29-SEP-2006 11:49:03 7200
26-SEP-2006 11:49:03 7200
5 rows selected.
>
> REM Should see 6 rows…all NUMBERS
> SELECT column3,COUNT(1) FROM jeff_test GROUP BY column3;
COLUMN3 COUNT(1)
———- ———-
1 6000
2 6000
4 6000
5 6000
3 6000
0 6000
6 rows selected.
>
> REM Now lets try and swap the partition and the table…
> REM It will fail with error…
> REM ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
>
> ALTER TABLE jeff_test_ptn
2 EXCHANGE PARTITION p001
3 WITH TABLE jeff_test
4 INCLUDING INDEXES
5 WITHOUT VALIDATION
6 /
ALTER TABLE jeff_test_ptn
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
>
> REM So, it only fails when the columns have the same datatypes.
>
> REM Now, lets say they are the same datatype and look at how bitmap indexes
> REM are affected in the PARTITION EXCHANGE process…
>
> REM First lets recreate the tables with COLUMN2 and COLUMN3
> REM having the same datatype…
> REM Drop the tables…
> DROP TABLE jeff_test
2 /
Table dropped.
> DROP TABLE jeff_test_ptn
2 /
Table dropped.
> REM Create the tables…
> CREATE TABLE jeff_test_ptn(partition_key_col NUMBER NOT NULL
2 ,column1 NUMBER NOT NULL
3 ,column3 NUMBER NOT NULL
4 ,column2 NUMBER NOT NULL
5 ,column4 NUMBER NOT NULL
6 )
7 PARTITION BY RANGE (partition_key_col)
8 (
9 PARTITION p001 VALUES LESS THAN (1000001)
10 ,PARTITION p002 VALUES LESS THAN (MAXVALUE)
11 )
12 /
Table created.
> CREATE TABLE jeff_test(partition_key_col NUMBER NOT NULL
2 ,column1 NUMBER NOT NULL
3 ,column2 NUMBER NOT NULL
4 ,column3 NUMBER NOT NULL
5 ,column4 NUMBER NOT NULL
6 )
7 /
Table created.
> REM Populate the table…
> INSERT /*+ APPEND */
2 INTO jeff_test_ptn(partition_key_col
3 ,column1
4 ,column2
5 ,column3
6 ,column4
7 )
8 SELECT idx
9 , MOD(idx,2)
10 , MOD(idx,3)
11 , MOD(idx,4)
12 , MOD(idx,2)
13 FROM (SELECT LEVEL idx FROM dual CONNECT BY LEVEL
COMMIT
2 /
Commit complete.
> INSERT /*+ APPEND */
2 INTO jeff_test(partition_key_col
3 ,column1
4 ,column2
5 ,column3
6 ,column4
7 )
8 SELECT idx
9 , MOD(idx,2)
10 , MOD(idx,5)
11 , MOD(idx,6)
12 , MOD(idx,2)
13 FROM (SELECT LEVEL idx FROM dual CONNECT BY LEVEL
COMMIT
2 /
Commit complete.
>
> REM Now lets create a bitmap index on COLUMN1 on both tables…
> CREATE BITMAP INDEX jtp1 ON jeff_test_ptn(column1) LOCAL
2 /
Index created.
> CREATE BITMAP INDEX jt1 ON jeff_test(column1)
2 /
Index created.
>
> REM …and now try PARTITION EXCHANGE…
> ALTER TABLE jeff_test_ptn
2 EXCHANGE PARTITION p001
3 WITH TABLE jeff_test
4 INCLUDING INDEXES
5 WITHOUT VALIDATION
6 /
Table altered.
> REM It works fine.
>
> REM Now lets create a bitmap index on COLUMN4 on both tables…
> CREATE BITMAP INDEX jtp4 ON jeff_test_ptn(column4) LOCAL
2 /
Index created.
> CREATE BITMAP INDEX jt4 ON jeff_test(column4)
2 /
Index created.
>
> REM …and now try PARTITION EXCHANGE…
> ALTER TABLE jeff_test_ptn
2 EXCHANGE PARTITION p001
3 WITH TABLE jeff_test
4 INCLUDING INDEXES
5 WITHOUT VALIDATION
6 /
Table altered.
>
> REM It works fine.
>
> REM Now lets create a bitmap index on COLUMN2 on both tables…
> CREATE BITMAP INDEX jtp2 ON jeff_test_ptn(column2) LOCAL
2 /
Index created.
> CREATE BITMAP INDEX jt2 ON jeff_test(column2)
2 /
Index created.
>
> REM …and now try PARTITION EXCHANGE…
> ALTER TABLE jeff_test_ptn
2 EXCHANGE PARTITION p001
3 WITH TABLE jeff_test
4 INCLUDING INDEXES
5 WITHOUT VALIDATION
6 /
WITH TABLE jeff_test
*
ERROR at line 3:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
Right, now what can we conclude from the above ?
1. If you don’t have indexes on your table then you can do an exchange even if the columns are in a different order – but you will silently transpose the columns. No errors are given and I think this is a bug.
2. You will only hit the problem in 1 if the columns which are transposed are of the same datatype – I’ve not checked for scale/precision/length – it may be, for example, that a VARCHAR2(20) and VARCHAR2(10) being transposed would raise an ORA-14097 error.
3. If you have indexes on columns which are transposed then the exchange will fail with ORA-14098. I don’t know whether this is a bitmap index specific thing as I’ve not tested it any further.
4. If you only have indexes on columns which are not transposed then you can do the exchange and there will be no errors – but your data is obviously transposed silently.