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

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

MING@ming(MING)> alter index   ID_PART1_PARTIAL modify partition p3 unusable;

Index altered.

MING@ming(MING)> update part1 set   name='yy' where id=1500;

2 rows updated.

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

MING@ming(MING)> alter index   ID_PART1_PARTIAL rebuild partition p2 online;

Index altered.

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    into   "MING"."PART1"  pa

rtition ("P2") select   *  from "MING"."PART1"   partition ("P2")  insert not   u

nique partial global indexes

delete from index_orphaned_entry$ where   indexobj#=:1

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.