oracle+12c+大表,oracle 12c之分区表不完全索引（二）

关于oracle 12c的不完全索引，这是第二篇，第一篇总体上描述了一下不完全索引，回顾地址：

之前描述过，不完全索引的状态是否可用，取决于indexing为on，还是off。

本文主要论述如下：

1.

Indexing属性不变的情况下能否修改索引状态

2.

修改indexing属性的时候，索引的状态修改行为探究

3.

间隔分区是否也能用不完全索引

实验一

实验准备

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.

这里就不在展示了，但是对于间隔分区表来说，不完全索引也是可用的。

总结

1. Indexing属性不变的情况下可以单独修改索引状态

2.修改分区indexing属性的时候，会在表分区上加独占锁，从而阻塞DML；

3

.

重建一个分区的索引：alter index ID_PART1_PARTIAL rebuild partition p2 parallel 2 online;

4.间隔分区也可以使用不完全索引

5.创建索引时如果使用了indexing full，修改分区indexing属性不会影响不完全索引的状态