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

共计 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 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！