PLSQL，Oracle 測試 ，建立局部分區索引，全局未分區索引 ，全局分區索引，以及失效重建

1. 在建立索引時如果不顯式指定global或local，則預設是global。
2. 在建立global索引時如果不顯式指定分區子句，則預設不分區(廢話)。

--建立測試表

DROP TABLE t_part;

create table t_part(object_id int,object_name varchar2(1000)) partition by range(object_id)

(

partition p1 values less than (1000),

partition p2 values less than (2000),

partition p3 values less than (3000),

partition p4 values less than (4000),

partition pm values less than (maxvalue)

);

--插入測試資料

INSERT INTO t_part SELECT ROWNUM,t.index_name FROM dba_indexes t;

commit;

--建立本地分區索引

create index idx_part_local on t_part(object_name) local;

--建立全局非分區索引

create index idx_part_global on t_part(object_id) global;

--删除分區前查詢索引狀态，全局非分區索引和本地分區索引都正常

select status,index_name from user_indexes s where index_name='IDX_PART_GLOBAL';

select status,index_name from user_ind_partitions s where index_name='IDX_PART_LOCAL';

--删除一個分區，或者，清除一個分區的資料，都會使全局未分區索引失效

alter table t_part drop partition p1;

--alter table t_part truncate partition p1;

--删除後再查詢索引狀态，全局非分區索引失效，本地分區索引沒有失效

select status,index_name from user_indexes s where index_name='IDX_PART_GLOBAL';

select status,index_name from user_ind_partitions s where index_name='IDX_PART_LOCAL';

--查詢不報錯

select * from t_part t where object_id > 35000;

--查詢報錯，ORA-01502: 索引 'SCOTT.IDX_PART_GLOBAL' 或這類索引的分區處于不可用狀态

select * from t_part t where object_id > 35000;

--重建失效索引

alter index idx_part_global rebuild;

--删除後再查詢索引狀态，全局非分區索引和本地分區索引都恢複正常

select status,index_name from user_indexes s where index_name='IDX_PART_GLOBAL';

select status,index_name from user_ind_partitions s where index_name='IDX_PART_LOCAL';

--在删除表分區的時候，可以通過以下指令進行索引重建

alter table t_part drop partition p2 UPDATE Global indexes; --(alter table t_part drop partition p2 UPDATE indexes;)

=================================================

--建立全局分區索引

drop index idx_part_global;

CREATE INDEX idx_part_global_full ON t_part (object_id)

GLOBAL PARTITION BY RANGE (object_id)

(PARTITION p1 VALUES LESS THAN (1000),

PARTITION p2 VALUES LESS THAN (3000),

PARTITION p3 VALUES LESS THAN (MAXVALUE)

);

--删除其中一個分區

alter table t_part drop partition p3;

--全局分區索引失效

select status,index_name from user_ind_partitions s where index_name='IDX_PART_GLOBAL_FULL';

--本地索引沒失效，不報錯

select * from t_part t where object_name = 'SYS_C003319';

--全局分區索引失效，報錯 ORA-01502: 索引 'SCOTT.IDX_PART_GLOBAL_FULL' 或這類索引的分區處于不可用狀态

select * from t_part t where object_id > 35000;

SELECT * FROM t_part Partition(p1)

SELECT * FROM dba_Indexes WHERE index_name='IDX_PART_GLOBAL_FULL' ;--索引基本資訊

SELECT * FROM dba_part_indexes WHERE index_name='IDX_PART_GLOBAL_FULL' ;--索引對應表，相關資訊，local/global，範圍分區，list分區

SELECT * FROM dba_ind_partitions WHERE index_name='IDX_PART_LOCAL' ;--索引對應分區資訊

SELECT * FROM dba_ind_subpartitions WHERE index_name='IDX_PART_LOCAL' ;--索引對應子分區資訊

SELECT TABLESPACE_NAME FROM DBA_INDEXES WHERE INDEX_NAME = 'IDX_PART_GLOBAL';