oracle 12c分区表不完全索引分析

本篇内容主要讲解“oracle 12c分区表不完全索引分析”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“oracle 12c分区表不完全索引分析”吧!实验准备create table part1(id int, code int,name varchar2(100))indexing offpartition 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 a30MING@ming(MING)> select PARTITION_NAME,indexing from dba_tab_partitions where table_owner=’MING’ AND TABLE_NAME=’PART1′;PARTITION_NAME INDE—————————— —-P1 OFFP2 OFFP3 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 A30MING@ming(MING)> select index_name,staTUS from user_indexes where table_name=’PART1′;INDEX_NAME STATUS—————————— ——–CODE_PART1_GLOBAL VALIDID_PART1_PARTIAL N/AMING@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 UNUSABLEP2 ID_PART1_PARTIAL UNUSABLEP3 ID_PART1_PARTIAL USABLEP2分区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 a30MING@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 UNUSABLEP2 ID_PART1_PARTIAL USABLEP3 ID_PART1_PARTIAL USABLEMING@ming(MING)> select PARTITION_NAME,indexing from dba_tab_partitions where table_owner=’MING’ AND TABLE_NAME=’PART1′;PARTITION_NAME INDE—————————— —-P1 OFFP2 OFFP3 ON重建某个分区的索引要用rebuild partition的方法。前面的实验已经得到，修改indexing属性会相应的更改索引的状态；通过上述实验，我们可以只针对某个分区重建索引，而且修改索引的状态不会改变indexing属性。当然也可以在indexing为on的时候，修改索引为unusableMING@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 NOWAITalter index “MING”.”CODE_PART1_GLOBAL” coalesce cleanupinsert 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″ partition (“P2”) select /*+ RELATIONAL(“PART1”) NO_PARALLEL */ * from “MING”.”PART1″ partition (“P2”) insert not unique partial global indexesdelete 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.查询M免费云主机域名ING@ming(MING)> col PARTITION_NAME for a30MING@ming(MING)> col INDEX_NAME for a30MING@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 USABLESYS_P420 ID_DAY_PART USABLESYS_P421 ID_DAY_PART USABLESYS_P422 ID_DAY_PART USABLEMING@ming(MING)> alter table DAY_PART modify partition SYS_P420 indexing off;Table altered.这里就不在展示了，但是对于间隔分区表来说，不完全索引也是可用的。到此，相信大家对“oracle 12c分区表不完全索引分析”有了更深的了解，不妨来实际操作一番吧！这里是百云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！

相关推荐: PostgreSQL 12 B-tree的改进是什么

这篇文章主要讲解了“P免费云主机域名ostgreSQL 12 B-tree的改进是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“PostgreSQL 12 B-tree的改进是什么”吧！创建数据表，创建索引…