本篇内容主要讲解“oracle 12c分区表不完全索引分析”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“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)>
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 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分区表不完全索引分析”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
原创文章，作者：carmelaweatherly，如若转载，请注明出处：https://blog.ytso.com/206386.html