OralceEXCHANGEPARTITION的示例

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了OralceEXCHANGEPARTITION的示例，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5422字，纯文字阅读大概需要8分钟。

内容图文

--创建分区表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

内容总结

以上是互联网集市为您收集整理的OralceEXCHANGEPARTITION的示例全部内容，希望文章能够帮你解决OralceEXCHANGEPARTITION的示例所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端