SET ECHO ON
REM Drop the tables...
DROP TABLE jeff_test
/
DROP TABLE jeff_test_ptn
/
REM Create the tables...
CREATE TABLE jeff_test_ptn(partition_key_col NUMBER        NOT NULL
                          ,column1           NUMBER        NOT NULL
                          ,column3           NUMBER        NOT NULL
                          ,column2           NUMBER        NOT NULL
                          ,column4           NUMBER        NOT NULL
                      )
PARTITION BY RANGE (partition_key_col)
(
 PARTITION p001 VALUES LESS THAN (1000001) 
,PARTITION p002 VALUES LESS THAN (MAXVALUE)
)
/
CREATE TABLE jeff_test(partition_key_col NUMBER        NOT NULL
                      ,column1           NUMBER        NOT NULL
                      ,column2           NUMBER        NOT NULL
                      ,column3           NUMBER        NOT NULL
                      ,column4           NUMBER        NOT NULL
                      )
/
REM Populate the table...
INSERT /*+ APPEND */
INTO jeff_test_ptn(partition_key_col
                  ,column1
                  ,column2
                  ,column3
                  ,column4
                  )
SELECT idx
,      MOD(idx,2)
,      MOD(idx,3)
,      MOD(idx,4)
,      MOD(idx,2)
FROM   (SELECT LEVEL idx FROM dual CONNECT BY LEVEL < 36001)
/
COMMIT
/
INSERT /*+ APPEND */
INTO jeff_test(partition_key_col
              ,column1
              ,column2
              ,column3
              ,column4
              )
SELECT idx
,      MOD(idx,2)
,      MOD(idx,5)
,      MOD(idx,6)
,      MOD(idx,2)
FROM   (SELECT LEVEL idx FROM dual CONNECT BY LEVEL < 36001)
/
COMMIT
/
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;

REM Should see 4 rows...all NUMBERS
SELECT column3,COUNT(1) FROM jeff_test_ptn GROUP BY column3;

REM Should see 5 rows...all NUMBERS
SELECT column2,COUNT(1) FROM jeff_test GROUP BY column2;

REM Should see 6 rows...all NUMBERS
SELECT column3,COUNT(1) FROM jeff_test GROUP BY column3;

REM Now lets try and swap the partition and the table...
ALTER TABLE jeff_test_ptn
EXCHANGE PARTITION p001
WITH TABLE jeff_test
INCLUDING INDEXES
WITHOUT VALIDATION
/

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;

REM Should see 6 rows...but we see 5 rows
SELECT column3,COUNT(1) FROM jeff_test_ptn GROUP BY column3;

REM Should see 3 rows...but we see 4 rows
SELECT column2,COUNT(1) FROM jeff_test GROUP BY column2;

REM Should see 4 rows...but we see 3 rows
SELECT column3,COUNT(1) FROM jeff_test GROUP BY column3;

REM Now, lets try again but with COLUMN2 and COLUMN3 
REM being of different datatypes...

REM Drop the tables...
DROP TABLE jeff_test
/
DROP TABLE jeff_test_ptn
/
REM Create the tables...
CREATE TABLE jeff_test_ptn(partition_key_col NUMBER        NOT NULL
                          ,column1           NUMBER        NOT NULL
                          ,column3           NUMBER        NOT NULL
                          ,column2           DATE          NOT NULL
                          ,column4           NUMBER        NOT NULL
                      )
PARTITION BY RANGE (partition_key_col)
(
 PARTITION p001 VALUES LESS THAN (1000001) 
,PARTITION p002 VALUES LESS THAN (MAXVALUE)
)
/
CREATE TABLE jeff_test(partition_key_col NUMBER        NOT NULL
                      ,column1           NUMBER        NOT NULL
                      ,column2           DATE          NOT NULL
                      ,column3           NUMBER        NOT NULL
                      ,column4           NUMBER        NOT NULL
                      )
/
REM Populate the table...
INSERT /*+ APPEND */
INTO jeff_test_ptn(partition_key_col
                  ,column1
                  ,column2
                  ,column3
                  ,column4
                  )
SELECT idx
,      MOD(idx,2)
,      SYSDATE + MOD(idx,3)
,      MOD(idx,4)
,      MOD(idx,2)
FROM   (SELECT LEVEL idx FROM dual CONNECT BY LEVEL < 36001)
/
COMMIT
/
INSERT /*+ APPEND */
INTO jeff_test(partition_key_col
              ,column1
              ,column2
              ,column3
              ,column4
              )
SELECT idx
,      MOD(idx,2)
,      SYSDATE + MOD(idx,5)
,      MOD(idx,6)
,      MOD(idx,2)
FROM   (SELECT LEVEL idx FROM dual CONNECT BY LEVEL < 36001)
/
COMMIT
/
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;

REM Should see 4 rows...all NUMBERS
SELECT column3,COUNT(1) FROM jeff_test_ptn GROUP BY column3;

REM Should see 5 rows...all DATES
SELECT column2,COUNT(1) FROM jeff_test GROUP BY column2;

REM Should see 6 rows...all NUMBERS
SELECT column3,COUNT(1) FROM jeff_test GROUP BY column3;

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
EXCHANGE PARTITION p001
WITH TABLE jeff_test
INCLUDING INDEXES
WITHOUT VALIDATION
/

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
/
DROP TABLE jeff_test_ptn
/
REM Create the tables...
CREATE TABLE jeff_test_ptn(partition_key_col NUMBER        NOT NULL
                          ,column1           NUMBER        NOT NULL
                          ,column3           NUMBER        NOT NULL
                          ,column2           NUMBER        NOT NULL
                          ,column4           NUMBER        NOT NULL
                      )
PARTITION BY RANGE (partition_key_col)
(
 PARTITION p001 VALUES LESS THAN (1000001) 
,PARTITION p002 VALUES LESS THAN (MAXVALUE)
)
/
CREATE TABLE jeff_test(partition_key_col NUMBER        NOT NULL
                      ,column1           NUMBER        NOT NULL
                      ,column2           NUMBER        NOT NULL
                      ,column3           NUMBER        NOT NULL
                      ,column4           NUMBER        NOT NULL
                      )
/
REM Populate the table...
INSERT /*+ APPEND */
INTO jeff_test_ptn(partition_key_col
                  ,column1
                  ,column2
                  ,column3
                  ,column4
                  )
SELECT idx
,      MOD(idx,2)
,      MOD(idx,3)
,      MOD(idx,4)
,      MOD(idx,2)
FROM   (SELECT LEVEL idx FROM dual CONNECT BY LEVEL < 36001)
/
COMMIT
/
INSERT /*+ APPEND */
INTO jeff_test(partition_key_col
              ,column1
              ,column2
              ,column3
              ,column4
              )
SELECT idx
,      MOD(idx,2)
,      MOD(idx,5)
,      MOD(idx,6)
,      MOD(idx,2)
FROM   (SELECT LEVEL idx FROM dual CONNECT BY LEVEL < 36001)
/
COMMIT
/

REM Now lets create a bitmap index on COLUMN1 on both tables...
CREATE BITMAP INDEX jtp1 ON jeff_test_ptn(column1) LOCAL
/
CREATE BITMAP INDEX jt1 ON jeff_test(column1)
/

REM ...and now try PARTITION EXCHANGE...
ALTER TABLE jeff_test_ptn
EXCHANGE PARTITION p001
WITH TABLE jeff_test
INCLUDING INDEXES
WITHOUT VALIDATION
/
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
/
CREATE BITMAP INDEX jt4 ON jeff_test(column4)
/

REM ...and now try PARTITION EXCHANGE...
ALTER TABLE jeff_test_ptn
EXCHANGE PARTITION p001
WITH TABLE jeff_test
INCLUDING INDEXES
WITHOUT VALIDATION
/

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
/
CREATE BITMAP INDEX jt2 ON jeff_test(column2)
/

REM ...and now try PARTITION EXCHANGE...
ALTER TABLE jeff_test_ptn
EXCHANGE PARTITION p001
WITH TABLE jeff_test
INCLUDING INDEXES
WITHOUT VALIDATION
/

REM It fails with error:
REM ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

REM So, not only will it transpose columns but it will also allow you
REM to create a bitmap on it under certain scenarios but not others.