Oralce EXCHANGE PARTITION 的示例

--创建分区表CREATE TABLE TEST(X INT,Y INT) PARTITION BY RANGE(X) ( PARTITION PART0 VALUES LESS THAN (100), PARTITION P

--创建分区表
CREATE TABLE TEST(X INT,Y INT)
PARTITION BY RANGE(X)
(
PARTITION PART0 VALUES LESS THAN (100),
PARTITION PART1 VALUES LESS THAN (MAXVALUE)
);
--创建索引
CREATE INDEX IDX_TEST_X ON TEST(X) LOCAL;
CREATE INDEX IDX_TEST_Y ON TEST(Y);

--创建交换堆表
CREATE TABLE TMP_TEST(X INT, Y INT);
--创建索引
CREATE INDEX IDX_TMP_TEST_X ON TMP_TEST(X);

--初始化分区表数据
BEGIN
FOR I IN 1..200 LOOP
INSERT INTO TEST VALUES(I,I-1);
END LOOP;
COMMIT;
END;
--初始化堆表数据
BEGIN
FOR I IN 1..50 LOOP
INSERT INTO TMP_TEST VALUES(I,I-1);
END LOOP;
COMMIT;
END;

--查看表的元数据
SQL> SELECT OBJECT_NAME,
2 SUBOBJECT_NAME,
3 OBJECT_ID,
4 DATA_OBJECT_ID,
5 OBJECT_TYPE,
6 STATUS
7 FROM DBA_OBJECTS
8 WHERE OBJECT_NAME IN ('TEST', 'TMP_TEST')
9 ORDER BY OBJECT_NAME;

OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE STATUS
-------------------- -------------------- ---------- -------------- ------------------- -------
TEST PART1 60040 60040 TABLE PARTITION VALID
TEST PART0 60039 60039 TABLE PARTITION VALID
TEST 60038 TABLE VALID
TMP_TEST 60045 60045 TABLE VALID

----索引的元数据
SQL> SELECT OBJECT_NAME,
2 SUBOBJECT_NAME,
3 OBJECT_ID,
4 DATA_OBJECT_ID,
5 OBJECT_TYPE,
6 STATUS
7 FROM DBA_OBJECTS
8 WHERE OBJECT_NAME IN ('IDX_TEST_X', 'IDX_TEST_Y','IDX_TMP_TEST_X');

OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE STATUS
-------------------- -------------------- ---------- -------------- ------------------- ------
IDX_TMP_TEST_X 60047 60047 INDEX VALID
IDX_TEST_Y 60044 60044 INDEX VALID
IDX_TEST_X 60041 INDEX VALID
IDX_TEST_X PART0 60042 60042 INDEX PARTITION VALID
IDX_TEST_X PART1 60043 60043 INDEX PARTITION VALID

--交换表及已有的索引
ALTER TABLE TEST EXCHANGE PARTITION PART0 WITH TABLE TMP_TEST INCLUDING INDEXES;

--查看数据已交换成功
SQL> SELECT COUNT(*) FROM TMP_TEST;

COUNT(*)
----------
99

SQL> SELECT COUNT(*) FROM TEST PARTITION(PART0);

COUNT(*)
----------
50
--查看表元数据的变化，可以得出结论exchange 只是交换的是数据段编号
SQL> SELECT OBJECT_NAME,
2 SUBOBJECT_NAME,
3 OBJECT_ID,
4 DATA_OBJECT_ID,
5 OBJECT_TYPE,
6 STATUS
7 FROM DBA_OBJECTS
8 WHERE OBJECT_NAME IN ('TEST', 'TMP_TEST')
9 ORDER BY OBJECT_NAME;

OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE STATUS
-------------------- -------------------- ---------- -------------- ------------------- -------
TEST PART1 60040 60040 TABLE PARTITION VALID
TEST PART0 60039 60045 TABLE PARTITION VALID
TEST 60038 TABLE VALID
TMP_TEST 60045 60039 TABLE VALID
--查看索引元数据的变化，可以看出index的变化:交换了段编号

SQL> SELECT OBJECT_NAME,
2 SUBOBJECT_NAME,
3 OBJECT_ID,
4 DATA_OBJECT_ID,
5 OBJECT_TYPE,
6 STATUS
7 FROM DBA_OBJECTS
8 WHERE OBJECT_NAME IN ('IDX_TEST_X', 'IDX_TEST_Y','IDX_TMP_TEST_X','IDX_TMP_TEST_Y');

OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE STATUS
-------------------- -------------------- ---------- -------------- ------------------- -------
IDX_TMP_TEST_X 60047 60042 INDEX VALID
IDX_TEST_Y 60044 60044 INDEX VALID
IDX_TEST_X 60041 INDEX VALID
IDX_TEST_X PART0 60042 60047 INDEX PARTITION VALID
IDX_TEST_X PART1 60043 60043 INDEX PARTITION VALID
--查看索引的状态
--发现分区表TEST的GLOBAL索引已不可用，需要重新创建，Local的分区索引显示为N/A，我们需要查询另外一个视图来确定是否可用
--经测试在交换分区的时候 加上 update indexes 则可以避免GLobal索引失效的情况。
SQL> SELECT INDEX_NAME,TABLE_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME IN ('TEST','TMP_TEST');

INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
IDX_TEST_X TEST N/A
IDX_TEST_Y TEST UNUSABLE
IDX_TMP_TEST_X TMP_TEST VALID
--LOCAL分区索引仍然是有效的
SQL> SELECT INDEX_NAME,STATUS FROM USER_IND_PARTITIONS WHERE INDEX_NAME IN ('IDX_TEST_X');

INDEX_NAME STATUS
------------------------------ --------
IDX_TEST_X USABLE
IDX_TEST_X USABLE