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

本篇内容主要讲解“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)> 
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分区表不完全索引分析”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!

原创文章,作者:carmelaweatherly,如若转载,请注明出处:https://blog.ytso.com/206386.html

(0)
上一篇 2021年12月3日
下一篇 2021年12月3日

相关推荐

发表回复

登录后才能评论