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
——————– ——————– ———- ————– ——————- 本文来源[email protected]搞@^&代*@码)网5——-
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

喜欢 (0)赏

[搞代码]

分享 (0)