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