前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >分区操作后索引的状态

分区操作后索引的状态

作者头像
数据和云
发布于 2020-02-27 15:20:33
7670
发布于 2020-02-27 15:20:33
举报
文章被收录于专栏：数据和云数据和云

导读：DDL操作是否会导致索引失效的原则上是看是否引起数据发生变化，如果分区的数据发生了改变，则索引需要失效才能保证结果的准确性，如果数据没有发生变化，则索引的状态不会变为UNUSABLE。

要对产品库上的分区进行SPLIT操作，于是首先评估一下SPLIT操作对索引的影响，结果发现测试的结果和文档上描述的不大一样。 在Oracle文档上对于分区的SPLIT操作是这样描述的：

Oracle invalidates any global indexes on heap-organized tables. You can update these indexes during this operation using the update_global_index_clause.

而测试的结果表明，无论是GLOBAL索引还是LOCAL索引，在进行分区操作后，索引是否变为UNUSABLE状态，是由索引数据是否发生变化决定的。 下面看具体的测试：

代码语言：javascript
复制

SQL> CREATE TABLE T_PARTITION (ID NUMBER, FIRST_NAME VARCHAR2(), LAST_NAME VARCHAR2())
    PARTITION BY RANGE (ID)
    (PARTITION P1 VALUES LESS THAN (),
    PARTITION P2 VALUES LESS THAN (),
    PARTITION P3 VALUES LESS THAN (MAXVALUE));
表已创建。
SQL> CREATE INDEX IND_T_PARTITION_F_NAME ON T_PARTITION(FIRST_NAME);
索引已创建。
SQL> CREATE INDEX IND_T_PARTITION_L_NAME ON T_PARTITION(LAST_NAME) LOCAL;
索引已创建。
SQL> INSERT INTO T_PARTITION VALUES (, 'A', 'A');
已创建  行。
SQL> INSERT INTO T_PARTITION VALUES (, 'B', 'B');
已创建  行。
SQL> COMMIT;
提交完成。
SQL> SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'T_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
IND_T_PARTITION_F_NAME         VALID
IND_T_PARTITION_L_NAME         N/A
SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
    WHERE INDEX_NAME = 'IND_T_PARTITION_L_NAME';
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_PARTITION_L_NAME         P1                             USABLE
IND_T_PARTITION_L_NAME         P2                             USABLE
IND_T_PARTITION_L_NAME         P3                             USABLE

首先创建一个分区表，分别在FIRST_NAME和LAST_NAME上建立GLOBAL索引和LOCAL索引。 下面对P3分区进行SPLIT操作，注意一点，P3分区的下限是200，而插入数据的分区键值均小于200，所以P3分区目前是空的。

代码语言：javascript
复制

SQL> SELECT COUNT(*) FROM T_PARTITION PARTITION (P3);
  COUNT(*)
----------
         
SQL> ALTER TABLE T_PARTITION SPLIT PARTITION P3 AT () INTO (PARTITION P3, PARTITION P4);
表已更改。
SQL> SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'T_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
IND_T_PARTITION_F_NAME         VALID
IND_T_PARTITION_L_NAME         N/A
SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
    WHERE INDEX_NAME = 'IND_T_PARTITION_L_NAME';
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_PARTITION_L_NAME         P1                             USABLE
IND_T_PARTITION_L_NAME         P2                             USABLE
IND_T_PARTITION_L_NAME         P3                             USABLE
IND_T_PARTITION_L_NAME         P4                             USABLE

当进行SPLIT操作的分区为空时，无论是GLOBAL索引还是LOCAL索引，状态均为USABLE。 如果在P3中插入一条记录，插入记录小于SPLIT操作的AT值，也就是说，在进行SPLIT操作后，插入记录会存在于分区键值小的分区中，这时如果对分区进行SPLIT操作：

代码语言：javascript
复制
SQL> ALTER TABLE T_PARTITION MERGE PARTITIONS P3, P4 INTO PARTITION P4;
表已更改。
SQL> SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'T_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
IND_T_PARTITION_F_NAME         VALID
IND_T_PARTITION_L_NAME         N/A
SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
    WHERE INDEX_NAME = 'IND_T_PARTITION_L_NAME';
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_PARTITION_L_NAME         P1                             USABLE
IND_T_PARTITION_L_NAME         P2                             USABLE
IND_T_PARTITION_L_NAME         P4                             USABLE
SQL> INSERT INTO T_PARTITION VALUES (, 'C', 'C');
已创建  行。
SQL> COMMIT;
提交完成。
SQL> ALTER TABLE T_PARTITION SPLIT PARTITION P4 AT () INTO (PARTITION P3, PARTITION P4);
表已更改。
SQL> SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'T_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
IND_T_PARTITION_F_NAME         VALID
IND_T_PARTITION_L_NAME         N/A
SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
    WHERE INDEX_NAME = 'IND_T_PARTITION_L_NAME';
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_PARTITION_L_NAME         P1                             USABLE
IND_T_PARTITION_L_NAME         P2                             USABLE
IND_T_PARTITION_L_NAME         P3                             USABLE
IND_T_PARTITION_L_NAME         P4                             USABLE

通过测试可以发现，全局索引和分区索引仍然没有发生状态的改变。这是由于表中数据的存储位置并没有发生变化。最多只是分区的名称发生了变化。表中数据位置不变，索引中记录的ROWID就不会发生变化，因此索引仍然是可用的。

当使用MERGE分区操作，将分区合并为三个分区时：

代码语言：javascript
复制
SQL> SELECT ROWID, ID FROM T_PARTITION WHERE ID = ;
ROWID                      ID
------------------ ----------
AAAKOJAAPAABd/kAAA        
SQL> ALTER TABLE T_PARTITION MERGE PARTITIONS P3, P4 INTO PARTITION P4;
表已更改。
SQL> SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'T_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
IND_T_PARTITION_F_NAME         UNUSABLE
IND_T_PARTITION_L_NAME         N/A
SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
    WHERE INDEX_NAME = 'IND_T_PARTITION_L_NAME';
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_PARTITION_L_NAME         P1                             USABLE
IND_T_PARTITION_L_NAME         P2                             USABLE
IND_T_PARTITION_L_NAME         P4                             UNUSABLE
SQL> SELECT ROWID, ID FROM T_PARTITION WHERE ID = ;
ROWID                      ID
------------------ ----------
AAAKOPAAQAABet8AAA

发现GLOBAL索引和LOCAL索引的P4分区的状态变为了UNUSABLE。这是由于MERGE操作的特点，MERGE操作会建立一个新的分区，将原始两个分区的数据放入新的分区，然后删除旧的分区。因此，MERGE操作会发生数据的转移，这一点通过ROWID的变化也可以看出来。 将UNUSABLE的索引重建后，再插入一条记录，使得SPLIT分区后，P4分区中的两条记录分别处于两个新分区中：

代码语言：javascript
复制

SQL> ALTER INDEX IND_T_PARTITION_F_NAME REBUILD;
索引已更改。
SQL> ALTER INDEX IND_T_PARTITION_L_NAME REBUILD PARTITION P4;
索引已更改。
SQL> SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'T_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
IND_T_PARTITION_F_NAME         VALID
IND_T_PARTITION_L_NAME         N/A
SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
    WHERE INDEX_NAME = 'IND_T_PARTITION_L_NAME';
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_PARTITION_L_NAME         P1                             USABLE
IND_T_PARTITION_L_NAME         P2                             USABLE
IND_T_PARTITION_L_NAME         P4                             USABLE
SQL> INSERT INTO T_PARTITION VALUES (, 'D', 'D');
已创建  行。
SQL> COMMIT;
提交完成。
SQL> ALTER TABLE T_PARTITION SPLIT PARTITION P4 AT () INTO (PARTITION P3, PARTITION P4);
表已更改。
SQL> SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'T_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
IND_T_PARTITION_F_NAME         UNUSABLE
IND_T_PARTITION_L_NAME         N/A
SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
    WHERE INDEX_NAME = 'IND_T_PARTITION_L_NAME';
INDEX_NAME                     PARTITION_NAME                 STATUS

------------------------------ ------------------------------ --------
IND_T_PARTITION_L_NAME         P1                             USABLE
IND_T_PARTITION_L_NAME         P2                             USABLE
IND_T_PARTITION_L_NAME         P3                             UNUSABLE
IND_T_PARTITION_L_NAME         P4                             UNUSABLE

和预料中的一样，无论是GLOBAL索引还是发生数据变化的两个分区的分区索引，状态都变为了UNUSABLE，下面的MERGE操作也是如此：

代码语言：javascript
复制
SQL> ALTER INDEX IND_T_PARTITION_F_NAME REBUILD;
索引已更改。
SQL> ALTER INDEX IND_T_PARTITION_L_NAME REBUILD PARTITION P3;
索引已更改。
SQL> ALTER INDEX IND_T_PARTITION_L_NAME REBUILD PARTITION P4;
索引已更改。
SQL> SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'T_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
IND_T_PARTITION_F_NAME         VALID
IND_T_PARTITION_L_NAME         N/A
SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
    WHERE INDEX_NAME = 'IND_T_PARTITION_L_NAME';
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_PARTITION_L_NAME         P1                             USABLE
IND_T_PARTITION_L_NAME         P2                             USABLE
IND_T_PARTITION_L_NAME         P3                             USABLE
IND_T_PARTITION_L_NAME         P4                             USABLE
SQL> ALTER TABLE T_PARTITION MERGE PARTITIONS P3, P4 INTO PARTITION P4;
表已更改。
SQL> SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'T_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
IND_T_PARTITION_F_NAME         UNUSABLE
IND_T_PARTITION_L_NAME         N/A
SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
    WHERE INDEX_NAME = 'IND_T_PARTITION_L_NAME';
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_PARTITION_L_NAME         P1                             USABLE
IND_T_PARTITION_L_NAME         P2                             USABLE
IND_T_PARTITION_L_NAME         P4                             UNUSABLE
SQL> ALTER INDEX IND_T_PARTITION_F_NAME REBUILD;
索引已更改。
SQL> ALTER INDEX IND_T_PARTITION_L_NAME REBUILD PARTITION P4;
索引已更改。
SQL> SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'T_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
IND_T_PARTITION_F_NAME         VALID

IND_T_PARTITION_L_NAME         N/A
SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
    WHERE INDEX_NAME = 'IND_T_PARTITION_L_NAME';
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_PARTITION_L_NAME         P1                             USABLE
IND_T_PARTITION_L_NAME         P2                             USABLE
IND_T_PARTITION_L_NAME         P4                             USABLE

下面删掉ID等于250的数据，使得SPLIT分区后，数据只保存在分区键值高的分区中：

代码语言：javascript
复制

SQL> DELETE T_PARTITION WHERE ID = ;
已删除  行。
SQL> COMMIT;
提交完成。
SQL> SELECT ROWID, ID FROM T_PARTITION WHERE ID = ;
ROWID                      ID
------------------ ----------
AAAKOaAAQAABet8AAB        
SQL> ALTER TABLE T_PARTITION SPLIT PARTITION P4 AT () INTO (PARTITION P3, PARTITION P4);
表已更改。
SQL> SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'T_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
IND_T_PARTITION_F_NAME         VALID
IND_T_PARTITION_L_NAME         N/A
SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
    WHERE INDEX_NAME = 'IND_T_PARTITION_L_NAME';
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_PARTITION_L_NAME         P1                             USABLE
IND_T_PARTITION_L_NAME         P2                             USABLE
IND_T_PARTITION_L_NAME         P3                             USABLE
IND_T_PARTITION_L_NAME         P4                             USABLE
SQL> SELECT ROWID, ID FROM T_PARTITION WHERE ID = ;
ROWID                      ID
------------------ ----------
AAAKOaAAQAABet8AAB

索引的状态仍然都是USABLE，数据也没有发生位置的变化，通过ROWID也可以证实这一点。在SPLIT操作中，Oracle可以判断出是否SPLIT的某个分区不包含数据，如果其中一个不包含数据，Oracle不需要转移数据，而直接将包含全部数据的新分区指向原始分区。 最后看一下TRUNCATE和DROP PARTITION的操作：

代码语言：javascript
复制
SQL> ALTER TABLE T_PARTITION TRUNCATE PARTITION P4;
表被截断。
SQL> SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'T_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
IND_T_PARTITION_F_NAME         UNUSABLE
IND_T_PARTITION_L_NAME         N/A
SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
    WHERE INDEX_NAME = 'IND_T_PARTITION_L_NAME';
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_PARTITION_L_NAME         P1                             USABLE
IND_T_PARTITION_L_NAME         P2                             USABLE
IND_T_PARTITION_L_NAME         P3                             USABLE
IND_T_PARTITION_L_NAME         P4                             USABLE
SQL> ALTER INDEX IND_T_PARTITION_F_NAME REBUILD;
索引已更改。
SQL> SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'T_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
IND_T_PARTITION_F_NAME         VALID
IND_T_PARTITION_L_NAME         N/A
SQL> INSERT INTO T_PARTITION VALUES (, 'D', 'D');
已创建  行。
SQL> COMMIT;
提交完成。
SQL> ALTER TABLE T_PARTITION DROP PARTITION P4;
表已更改。
SQL> SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'T_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
IND_T_PARTITION_F_NAME         UNUSABLE
IND_T_PARTITION_L_NAME         N/A
SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
    WHERE INDEX_NAME = 'IND_T_PARTITION_L_NAME';
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_PARTITION_L_NAME         P1                             USABLE
IND_T_PARTITION_L_NAME         P2                             USABLE
IND_T_PARTITION_L_NAME         P3                             USABLE
SQL> ALTER INDEX IND_T_PARTITION_F_NAME REBUILD;
索引已更改。
SQL> SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'T_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
IND_T_PARTITION_F_NAME         VALID
IND_T_PARTITION_L_NAME         N/A

上面是TRUNCATE或DROP分区时，分区中包含数据的情况，这个时候只有GLOBAL索引会受分区影响而改变状态。可以推断，如果分区中不包含数据，那么无论是TRUNCATE分区还是DROP分区，都不会影响任何的索引：

代码语言：javascript
复制
SQL> ALTER TABLE T_PARTITION ADD PARTITION P4 VALUES LESS THAN (MAXVALUE);
表已更改。
SQL> SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'T_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
IND_T_PARTITION_F_NAME         VALID
IND_T_PARTITION_L_NAME         N/A
SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
    WHERE INDEX_NAME = 'IND_T_PARTITION_L_NAME';
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_PARTITION_L_NAME         P1                             USABLE
IND_T_PARTITION_L_NAME         P2                             USABLE
IND_T_PARTITION_L_NAME         P3                             USABLE
IND_T_PARTITION_L_NAME         P4                             USABLE
SQL> ALTER TABLE T_PARTITION TRUNCATE PARTITION P4;
表被截断。
SQL> SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'T_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
IND_T_PARTITION_F_NAME         VALID
IND_T_PARTITION_L_NAME         N/A
SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
    WHERE INDEX_NAME = 'IND_T_PARTITION_L_NAME';
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_PARTITION_L_NAME         P1                             USABLE
IND_T_PARTITION_L_NAME         P2                             USABLE
IND_T_PARTITION_L_NAME         P3                             USABLE
IND_T_PARTITION_L_NAME         P4                             USABLE
SQL> ALTER TABLE T_PARTITION DROP PARTITION P4;
表已更改。
SQL> SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'T_PARTITION';
INDEX_NAME                     STATUS
------------------------------ --------
IND_T_PARTITION_F_NAME         VALID
IND_T_PARTITION_L_NAME         N/A
SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS
    WHERE INDEX_NAME = 'IND_T_PARTITION_L_NAME';
INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IND_T_PARTITION_L_NAME         P1                             USABLE
IND_T_PARTITION_L_NAME         P2                             USABLE
IND_T_PARTITION_L_NAME         P3                             USABLE

最后总结一下：当发生分区操作时，无论是GLOBAL索引还是LOCAL索引，索引状态的变化都只和索引中数据是否发生了变化有关。而LOCAL索引的优势就体现在这一点，对于TRUNCATE和DROP等分区操作，根本不会影响LOCAL索引状态，而对于SPLIT和MERGE操作，也只是会影响到操作涉及的分区。而对于GLOBAL索引，只有发生数据位置的变化，则会影响整个索引。 当然指定UPDATE GLOBAL INDEX语句可以同步更新GLOBAL索引，但是对于LOCAL索引并没有同步维护的方法。所以，最好在操作前对操作会产生何种影响了然于胸，在进行操作的时候才能更加得心应手。 比如，对于SPLIT操作，尤其是对包含MAXVALUE的分区进行的SPLIT操作，是分区表经常会碰到的操作，这个操作最好在分区中未包含数据时进行，如果要操作的分区已经包含了数据，最好可以通过SPLIT操作将现存分区中所有数据划分到一个分区中，这样对系统影响最小，性能也最高。而MERGE分区操作，则应该尽可能的避免，除非是MERGE两个空的分区，否则都会影响索引的可用性。

原文：墨天轮（https://www.modb.pro/db/15428）

本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2020-02-26，如有侵权请联系 cloudcommunity@tencent.com 删除
javascript
oracle

本文分享自 数据和云 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ，欢迎热爱写作的你一起参与！

javascript
oracle
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2024 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论