共计 4384 个字符，预计需要花费 11 分钟才能阅读完成。
本篇内容主要讲解“oracle 12c 分区表不完全索引分析”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“oracle 12c 分区表不完全索引分析”吧!
实验一
实验准备
create table part1
(id int, code int,name varchar2(100))
indexing off
partition by range (id)
(partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (3000) indexing on
);
MING@ming(MING) col partition_name for a30
MING@ming(MING) select PARTITION_NAME,indexing from dba_tab_partitions where table_owner= MING AND TABLE_NAME= PART1
PARTITION_NAME INDE
—————————— —-
P1 OFF
P2 OFF
P3 ON
创建索引
MING@ming(MING) create index code_part1_global on part1(code) global indexing partial;
Index created.
MING@ming(MING) create index id_part1_partial on part1(id) local indexing partial;
Index created.
索引状态
MING@ming(MING) COL INDEX_NAME FOR A30
MING@ming(MING) select index_name,staTUS from user_indexes where table_name= PART1
INDEX_NAME STATUS
—————————— ——–
CODE_PART1_GLOBAL VALID
ID_PART1_PARTIAL N/A
MING@ming(MING) SELECT PARTITION_NAME, INDEX_NAME,STATUS FROM USER_IND_PARTITIONS WHERE INDEX_NAME= ID_PART1_PARTIAL
PARTITION_NAME INDEX_NAME STATUS
—————————— —————————— ——–
P1 ID_PART1_PARTIAL UNUSABLE
P2 ID_PART1_PARTIAL UNUSABLE
P3 ID_PART1_PARTIAL USABLE
P2 分区 ID_PART1_PARTIAL 索引是 unusable 的，重建这个索引
MING@ming(MING)
alter index ID_PART1_PARTIAL rebuild partition p2 parallel 2 online;
Index altered.
MING@ming(MING) col partition_name for a30
MING@ming(MING) SELECT PARTITION_NAME, INDEX_NAME,STATUS FROM USER_IND_PARTITIONS WHERE INDEX_NAME= ID_PART1_PARTIAL
PARTITION_NAME INDEX_NAME STATUS
—————————— —————————— ——–
P1 ID_PART1_PARTIAL UNUSABLE
P2 ID_PART1_PARTIAL USABLE
P3 ID_PART1_PARTIAL USABLE
MING@ming(MING) select PARTITION_NAME,indexing from dba_tab_partitions where table_owner= MING AND TABLE_NAME= PART1
PARTITION_NAME INDE
—————————— —-
P1 OFF
P2 OFF
P3 ON
重建某个分区的索引要用 rebuild partition 的方法。
前面的实验已经得到，修改 indexing 属性会相应的更改索引的状态；通过上述实验，我们可以只针对某个分区重建索引，而且修改索引的状态不会改变 indexing 属性。
当然也可以在 indexing 为 on 的时候，修改索引为 unusable
MING@ming(MING) alter index ID_PART1_PARTIAL modify partition p3 unusable;
Index altered.
实验二
修改 indexing 属性的时候，索引的状态修改行为探究
把 ID_PART1_PARTIAL 索引删掉后重建，那么 P2 分区是 UNUSABLE。
P2 分区数据开启事务
MING@ming(MING) update part1 set name= yy where id=1500;
2 rows updated.
新开会话修改 indexing 属性
MING@ming(MING) alter table part1 modify partition p2 indexing on;
alter table part1 modify partition p2 indexing on
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
这说明修改分区 indexing，其上的索引不是以 online 的方式重建的，生产环境如果有频繁的 DML 事务，那么将会失败。这时候可以采上面实验中的方法，只针对索引，状态修改为 usable，然后找合适的时机修改 indexing 属性。
MING@ming(MING) alter index ID_PART1_PARTIAL rebuild partition p2 online;
Index altered.
针对 alter table part1 modify partition p2 indexing on 的 10046 事件，部分递归 sql 如下：
LOCK TABLE PART1 PARTITION (P2) IN EXCLUSIVE MODE NOWAIT
alter index MING . CODE_PART1_GLOBAL coalesce cleanup
insert into index_orphaned_entry$ (indexobj#, tabpartdobj#, hidden) values (:1, :2, :3)
insert /*+ RELATIONAL(PART1) NO_PARALLEL APPEND NESTED_TABLE_SET_SETID NO_REF_CASCADE */ into MING . PART1 pa
rtition (P2) select /*+ RELATIONAL(PART1) NO_PARALLEL */ * from MING . PART1 partition (P2) insert not u
nique partial global indexes
delete from index_orphaned_entry$ where indexobj#=:1
可以看到修改 indexing 属性的时候，会获得一个独占锁，这样就是当有活动事务的时候修改 indexing 报错的原因了。
实验三
间隔分区是否也能使用不完全索引呢？
创建间隔分区表
MING@ming(MING) create table day_part (id number,eitime date)
2
indexing off
3 partition by range(eitime)
4 interval (numtodsinterval(3, day))
5 (
6 partition p1 values less than (to_date( 2000-01-01 , yyyy-mm-dd))
7 );
Table created.
创建成功！
插入数据并创建索引
MING@ming(MING) insert into day_part values(1,sysdate);
MING@ming(MING) insert into day_part values(2,sysdate);
MING@ming(MING) insert into day_part values(2,sysdate+5);
MING@ming(MING) insert into day_part values(2,sysdate+10);
MING@ming(MING) commit;
MING@ming(MING) create index id_day_part on day_part(id) local indexing partial;
Index created.
查询
MING@ming(MING) col PARTITION_NAME for a30
MING@ming(MING) col INDEX_NAME for a30
MING@ming(MING) SELECT PARTITION_NAME, INDEX_NAME,STATUS FROM USER_IND_PARTITIONS WHERE INDEX_NAME= ID_DAY_PART
PARTITION_NAME INDEX_NAME STATUS
—————————— —————————— ——–
P1 ID_DAY_PART USABLE
SYS_P420 ID_DAY_PART USABLE
SYS_P421 ID_DAY_PART USABLE
SYS_P422 ID_DAY_PART USABLE
MING@ming(MING) alter table DAY_PART modify partition SYS_P420 indexing off;
Table altered.
这里就不在展示了，但是对于间隔分区表来说，不完全索引也是可用的。
到此，相信大家对“oracle 12c 分区表不完全索引分析”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
