oracle 分区表维护导致全局分区索引失效情况
接上篇本地索引的测试。
对于分区索引，需要区分创建的是全局索引，或本地索引：
- 全局索引(global index)：即可以分区，也可以不分区。即可以建range 分区，也可以建hash 分区，即可建于分区表，又可创建于非分区表上，就是说，全局索引是完全独立的，因此它也需要我们更多的维护操作。
- 本地索引(local index)：其分区形式与表的分区完全相同，依赖列相同，存储属性也相同。对于本地索引，其索引分区的维护自动进行，就如上篇测试，add/drop/truncate 表的分区时，本地索引会自动维护其索引分区，split/merge/move 表的分区时，本地索引不会自动维护其索引分。
可以利用视图user_part_indexes.LOCALITY中的字段来查看使本地索引还是全局索引。
特性
- 对于local 索引分区而言，其分区形式完全依赖于索引所在表的分区形式，不管从创建语法还是理解难度均无技术含量。
- 注意，在创建索引时如果不显式指定global 或local，则默认是global。
- 注意，在创建global 索引时如果不显式指定分区子句，则默认不分区(废话)。
- local 索引的分区维护完全依赖于其索引所在表。不过呢分区名称，以及分区所在表空间等信息是可以自定义的。
SQL> create index IDX_PARTI_RANGE_ID on T_PARTITION_RANGE(id);
Index created.
SQL> select index_name, partitioning_type, partition_count,LOCALITY,ALIGNMENT
2 From user_part_indexes
3 where index_name = 'IDX_PARTI_RANGE_ID';
no rows selected
--查询是否分区索引
SQL> select partition_name,high_value,tablespace_name,status from user_ind_partitions
2 where index_name='IDX_PARTI_RANGE_ID';
no rows selected
SQL> select owner,index_name,index_type,table_owner,table_name,table_type,partitioned from dba_indexes where index_name in('IDX_PARTI_RANGE_ID');
SQL> set pages 1000 lines 1000
OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE PAR
------------------------------ ------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- ---
SCOTT IDX_PARTI_RANGE_ID NORMAL SCOTT T_PARTITION_RANGE TABLE NO
复制查询发现全局索引不在视图dba_ind_partitions中。查询得索引IDX_PARTI_RANGE_ID 不是分区索引。
在创建索引时如果不显式指定global 或local，则默认是global。 其实和非分区表的普通索引就是一样的。
在创建global 索引时如果不显式指定分区子句，则默认不分区。
SQL> drop index idx_parti_range_id;
Index dropped.
SQL> create index idx_parti_range_id on t_partition_range(id)
2 global partition by range(id)(
3 partition i_range_p1 values less than (10) tablespace zhuo,
4 partition i_range_p2 values less than (40) tablespace zhuo,
5 partition i_range_pmax values less than (maxvalue) tablespace zhuo);
Index created.
SQL>
SQL> select index_name, partitioning_type, partition_count,LOCALITY,ALIGNMENT
2 From user_part_indexes
3 where index_name = 'IDX_PARTI_RANGE_ID';
INDEX_NAME PARTITION PARTITION_COUNT LOCALI ALIGNMENT
------------------------------ --------- --------------- ------ ------------
IDX_PARTI_RANGE_ID RANGE 3 GLOBAL PREFIXED
SQL>
SQL> select partition_name,high_value,tablespace_name,status from user_ind_partitions
2 where index_name='IDX_PARTI_RANGE_ID';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME STATUS
------------------------------ -------------------------------------------------------------------------------- ------------------------------ --------
I_RANGE_P1 10 ZHUO USABLE
I_RANGE_P2 40 ZHUO USABLE
I_RANGE_PMAX MAXVALUE ZHUO USABLE
复制local 索引的分区维护完全依赖于其索引所在表。不过呢分区名称，以及分区所在表空间等信息是可以自定义的，例如:
SQL> create index IDX_PARTI_RANGE_ID ON T_PARTITION_RANGE(id) local (
2 partition i_range_p1 tablespace zhuo,
3 partition i_range_p2 tablespace zhuo,
4 partition test tablespace zhuo,
5 partition i_range_pmax tablespace zhuo
6 );
Index created.
SQL> select index_name, partitioning_type, partition_count,LOCALITY,ALIGNMENT
2 From user_part_indexes
3 where index_name = 'IDX_PARTI_RANGE_ID';
INDEX_NAME PARTITION PARTITION_COUNT LOCALI ALIGNMENT
------------------------------ --------- --------------- ------ ------------
IDX_PARTI_RANGE_ID RANGE 4 LOCAL PREFIXED
SQL>
SQL> select partition_name,high_value,tablespace_name,status from user_ind_partitions
2 where index_name='IDX_PARTI_RANGE_ID';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME STATUS
------------------------------ -------------------------------------------------------------------------------- ------------------------------ --------
I_RANGE_P1 10 ZHUO USABLE
I_RANGE_P2 20 ZHUO USABLE
I_RANGE_PMAX MAXVALUE ZHUO USABLE
TEST 30 ZHUO USABLE
复制测试环境准备
环境信息：
单机oracle 11.2.0.4.0
os版本：RHEL 6.8.
drop table t_partition_range;
create table t_partition_range (id number,name varchar2(50))
partition by range(id)(
partition t_range_p1 values less than (10),
partition t_range_p2 values less than (20),
partition t_range_p3 values less than (30),
partition t_range_pmax values less than (maxvalue)
);
select table_name,partitioning_type,partition_count
From user_part_tables
where table_name=‘T_PARTITION_RANGE’;
select partition_name,high_value,tablespace_name
from user_tab_partitions
where table_name=‘T_PARTITION_RANGE’
order by partition_position;
create index idx_parti_range_id on t_partition_range(id)
global partition by range(id)(
partition i_range_p1 values less than (10) tablespace zhuo,
partition i_range_p2 values less than (20) tablespace zhuo,
partition i_range_p3 values less than (30) tablespace zhuo,
partition i_range_pmax values less than (maxvalue) tablespace zhuo);
select index_name, partitioning_type, partition_count,LOCALITY,ALIGNMENT
From user_part_indexes
where index_name = ‘IDX_PARTI_RANGE_ID’;
select partition_name,high_value,tablespace_name,status from user_ind_partitions
where index_name=‘IDX_PARTI_RANGE_ID’;
insert into t_partition_range values(4,‘ddd’);
insert into t_partition_range values(13,‘asdewr’);
insert into t_partition_range values(22,‘dasd’);
insert into t_partition_range values(34,‘aasd’);
commit;
select id,name,rowid from t_partition_range;
alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_P1;
alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_P2;
alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_P3;
alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_PMAX;
alter table t_partition_range split partition t_range_p1 at (25) into
(partition t_range_p1,
partition t_range_p4);
alter table t_partition_range merge partitions t_range_p6,t_range_p7 into partition t_range_p10;
split
和上一篇local的测试思路一致。
第一种情况
SQL> select index_name, partitioning_type, partition_count,LOCALITY,ALIGNMENT
2 From user_part_indexes
3 where index_name = 'IDX_PARTI_RANGE_ID';
INDEX_NAME PARTITION PARTITION_COUNT LOCALI ALIGNMENT
------------------------------ --------- --------------- ------ ------------
IDX_PARTI_RANGE_ID RANGE 4 GLOBAL PREFIXED
SQL>
SQL> select partition_name,high_value,tablespace_name,status from user_ind_partitions
2 where index_name='IDX_PARTI_RANGE_ID';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME STATUS
------------------------------ ---------- ------------------------------ --------
I_RANGE_P1 10 ZHUO USABLE
I_RANGE_P2 20 ZHUO USABLE
I_RANGE_P3 30 ZHUO USABLE
I_RANGE_PMAX MAXVALUE ZHUO USABLE
SQL> select id,name,rowid from t_partition_range;
ID NAME ROWID
---------- -------------------------------------------------- ------------------
4 ddd AAATynAAEAAACErAAA
13 asdewr AAATyoAAEAAAAErAAA
22 dasd AAATypAAEAAABErAAA
34 aasd AAATyqAAEAAABkrAAA
SQL> select * from t_partition_range partition (T_RANGE_P3);
ID NAME
---------- --------------------------------------------------
22 dasd
SQL> alter table t_partition_range split partition t_range_p3 at (25) into
2 (partition t_range_p3,
3 partition t_range_p4);
Table altered.
SQL> select index_name, partitioning_type, partition_count,LOCALITY,ALIGNMENT
2 From user_part_indexes
3 where index_name = 'IDX_PARTI_RANGE_ID';
INDEX_NAME PARTITION PARTITION_COUNT LOCALI ALIGNMENT
------------------------------ --------- --------------- ------ ------------
IDX_PARTI_RANGE_ID RANGE 4 GLOBAL PREFIXED
--此处可以看出是global索引。
SQL> select partition_name,high_value,tablespace_name,status from user_ind_partitions
2 where index_name='IDX_PARTI_RANGE_ID';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME STATUS
------------------------------ ---------- ------------------------------ --------
I_RANGE_P1 10 ZHUO USABLE
I_RANGE_P2 20 ZHUO USABLE
I_RANGE_P3 30 ZHUO USABLE
I_RANGE_PMAX MAXVALUE ZHUO USABLE
--可以看到，split拆分完分区后，global索引的分区数量没变化，而不像local索引一样会自动维护创建新的索引。global索引的结构不一定是和表的分区结构一致的。
SQL> select table_name,partitioning_type,partition_count
2 From user_part_tables
3 where table_name='T_PARTITION_RANGE';
TABLE_NAME PARTITION PARTITION_COUNT
------------------------------ --------- ---------------
T_PARTITION_RANGE RANGE 5
SQL> select partition_name,high_value,tablespace_name
2 from user_tab_partitions
3 where table_name='T_PARTITION_RANGE'
4 order by partition_position;
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------- ------------------------------
T_RANGE_P1 10 USERS
T_RANGE_P2 20 USERS
T_RANGE_P3 25 USERS
T_RANGE_P4 30 USERS
T_RANGE_PMAX MAXVALUE USERS
--表已经拆分
SQL> select id,name,rowid from t_partition_range;
ID NAME ROWID
---------- -------------------------------------------------- ------------------
4 ddd AAATynAAEAAACErAAA
13 asdewr AAATyoAAEAAAAErAAA
22 dasd AAATypAAEAAABErAAA
34 aasd AAATyqAAEAAABkrAAA
复制
第二种情况：
SQL> alter table t_partition_range split partition t_range_p2 at (12) into
2 (partition t_range_p2,
3 partition t_range_p5);
Table altered.
SQL> select table_name,partitioning_type,partition_count
2 From user_part_tables
3 where table_name='T_PARTITION_RANGE';
TABLE_NAME PARTITION PARTITION_COUNT
------------------------------ --------- ---------------
T_PARTITION_RANGE RANGE 6
SQL> select partition_name,high_value,tablespace_name
2 from user_tab_partitions
3 where table_name='T_PARTITION_RANGE'
4 order by partition_position;
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------- ------------------------------
T_RANGE_P1 10 USERS
T_RANGE_P2 12 USERS
T_RANGE_P5 20 USERS
T_RANGE_P3 25 USERS
T_RANGE_P4 30 USERS
T_RANGE_PMAX MAXVALUE USERS
6 rows selected.
SQL> select partition_name,high_value,tablespace_name,status from user_ind_partitions
2 where index_name='IDX_PARTI_RANGE_ID';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME STATUS
------------------------------ ---------- ------------------------------ --------
I_RANGE_P1 10 ZHUO USABLE
I_RANGE_P2 20 ZHUO USABLE
I_RANGE_P3 30 ZHUO USABLE
I_RANGE_PMAX MAXVALUE ZHUO USABLE
SQL> select id,name,rowid from t_partition_range;
ID NAME ROWID
---------- -------------------------------------------------- ------------------
4 ddd AAATynAAEAAACErAAA
13 asdewr AAATyoAAEAAAAErAAA
22 dasd AAATypAAEAAABErAAA
34 aasd AAATyqAAEAAABkrAAA
复制
第三种情况：
SQL> insert into t_partition_range values(40,'saewfwe');
1 row created.
SQL> commit;
Commit complete.
SQL> select id,name,rowid from t_partition_range;
ID NAME ROWID
---------- -------------------------------------------------- ------------------
4 ddd AAATynAAEAAACErAAA
13 asdewr AAATyoAAEAAAAErAAA
22 dasd AAATypAAEAAABErAAA
34 aasd AAATyqAAEAAABkrAAA
40 saewfwe AAATyqAAEAAABkrAAB
SQL> alter table t_partition_range split partition T_RANGE_PMAX at (35) into
2 (partition t_range_p6,
3 partition T_RANGE_PMAX);
Table altered.
SQL> select partition_name,high_value,tablespace_name,status from user_ind_partitions
2 where index_name='IDX_PARTI_RANGE_ID';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME STATUS
------------------------------ ---------- ------------------------------ --------
I_RANGE_P1 10 ZHUO UNUSABLE
I_RANGE_P2 20 ZHUO UNUSABLE
I_RANGE_P3 30 ZHUO UNUSABLE
I_RANGE_PMAX MAXVALUE ZHUO UNUSABLE
--此处已经变为unusable。
SQL> select table_name,partitioning_type,partition_count
2 From user_part_tables
3 where table_name='T_PARTITION_RANGE';
TABLE_NAME PARTITION PARTITION_COUNT
------------------------------ --------- ---------------
T_PARTITION_RANGE RANGE 7
SQL> select partition_name,high_value,tablespace_name
2 from user_tab_partitions
3 where table_name='T_PARTITION_RANGE'
4 order by partition_position;
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------- ------------------------------
T_RANGE_P1 10 USERS
T_RANGE_P2 12 USERS
T_RANGE_P5 20 USERS
T_RANGE_P3 25 USERS
T_RANGE_P4 30 USERS
T_RANGE_P6 35 USERS
T_RANGE_PMAX MAXVALUE USERS
7 rows selected.
SQL> select id,name,rowid from t_partition_range;
ID NAME ROWID
---------- -------------------------------------------------- ------------------
4 ddd AAATynAAEAAACErAAA
13 asdewr AAATyoAAEAAAAErAAA
22 dasd AAATypAAEAAABErAAA
34 aasd AAATy0AAEAAABUSAAA
40 saewfwe AAATy1AAEAAAAkSAAA
--rowid发生变化。
复制
我们把pmax拆分成了p6和pmax两个分区，会看到，p6和pmax上面的local索引已经失效，经查询表，会发现，表里面的数据：id=34和id=40的两条数据，经过拆分，rowid（发生了变化，因为rowid发生了变化，那么上面的索引肯定会失效（键值与rowid）。
和local索引失效的情况一摸一样。
总结上面情况：可知官方文档说的并不是很严谨。对于global索引，不是说我们要拆分的分区中有数据，拆分后对应的索引就会失效。而是要看我们分区中的数据分布，和我们要拆分的取得值，进行比较，如果都是一边倒，即分区里面的数据都比拆分的值大，或者小，那么拆分后，分区对应的索引不会失效。如果分区里面的值比拆分的值大于和小于都有，那么拆分后，分区对应的索引都会失效，因为表数据的rowid发生变化，而且使索引所有的分区都失效。并不一定只要split，就会失效。
在实际情况中，一般在待拆分值的两边都会有值，所以一般网上都会说split分区后，global索引会失效，就是这个原因。
merge
分区索引不能够将其作为整体重建，必须对每个分区重建
SQL> alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_P1;
Index altered.
SQL> select index_name, partitioning_type, partition_count,LOCALITY,ALIGNMENT
2 From user_part_indexes
3 where index_name = 'IDX_PARTI_RANGE_ID';
INDEX_NAME PARTITION PARTITION_COUNT LOCALI ALIGNMENT
------------------------------ --------- --------------- ------ ------------
IDX_PARTI_RANGE_ID RANGE 4 GLOBAL PREFIXED
SQL> SQL> select partition_name,high_value,tablespace_name,status from user_ind_partitions
2 where index_name='IDX_PARTI_RANGE_ID';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME STATUS
------------------------------ ---------- ------------------------------ --------
I_RANGE_P1 10 ZHUO USABLE
I_RANGE_P2 20 ZHUO UNUSABLE
I_RANGE_P3 30 ZHUO UNUSABLE
I_RANGE_PMAX MAXVALUE ZHUO UNUSABLE
SQL> alter index IDX_PARTI_RANGE_ID rebuild parallel 6 nologging online;
alter index IDX_PARTI_RANGE_ID rebuild parallel 6 nologging online
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole
SQL> alter index IDX_PARTI_RANGE_ID rebuild;
alter index IDX_PARTI_RANGE_ID rebuild
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole
SQL> alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_P2;
Index altered.
SQL> alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_P3;
Index altered.
SQL> alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_PMAX;
Index altered.
SQL> select index_name, partitioning_type, partition_count,LOCALITY,ALIGNMENT
2 From user_part_indexes
3 where index_name = 'IDX_PARTI_RANGE_ID';
INDEX_NAME PARTITION PARTITION_COUNT LOCALI ALIGNMENT
------------------------------ --------- --------------- ------ ------------
IDX_PARTI_RANGE_ID RANGE 4 GLOBAL PREFIXED
SQL>
SQL> select partition_name,high_value,tablespace_name,status from user_ind_partitions
2 where index_name='IDX_PARTI_RANGE_ID';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME STATUS
------------------------------ ---------- ------------------------------ --------
I_RANGE_P1 10 ZHUO USABLE
I_RANGE_P2 20 ZHUO USABLE
I_RANGE_P3 30 ZHUO USABLE
I_RANGE_PMAX MAXVALUE ZHUO USABLE
复制SQL> alter table t_partition_range merge partitions t_range_p2,t_range_p5 into partition t_range_p8;
Table altered.
SQL> select partition_name,high_value,tablespace_name,status from user_ind_partitions
2 where index_name='IDX_PARTI_RANGE_ID';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME STATUS
------------------------------ ---------- ------------------------------ --------
I_RANGE_P1 10 ZHUO UNUSABLE
I_RANGE_P2 20 ZHUO UNUSABLE
I_RANGE_P3 30 ZHUO UNUSABLE
I_RANGE_PMAX MAXVALUE ZHUO UNUSABLE
SQL> alter table t_partition_range merge partitions t_range_p3,t_range_p4 into partition t_range_p9;
Table altered.
SQL> select index_name, partitioning_type, partition_count,LOCALITY,ALIGNMENT
2 From user_part_indexes
3 where index_name = 'IDX_PARTI_RANGE_ID';
INDEX_NAME PARTITION PARTITION_COUNT LOCALI ALIGNMENT
------------------------------ --------- --------------- ------ ------------
IDX_PARTI_RANGE_ID RANGE 4 GLOBAL PREFIXED
SQL> select partition_name,high_value,tablespace_name,status from user_ind_partitions
2 where index_name='IDX_PARTI_RANGE_ID';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME STATUS
------------------------------ ---------- ------------------------------ --------
I_RANGE_P1 10 ZHUO UNUSABLE
I_RANGE_P2 20 ZHUO UNUSABLE
I_RANGE_P3 30 ZHUO UNUSABLE
I_RANGE_PMAX MAXVALUE ZHUO UNUSABLE
SQL> alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_P1;
alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_P2;
alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_P3;
Index altered.
SQL>
Index altered.
SQL>
Index altered.
SQL> alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_PMAX;
Index altered.
SQL> select table_name,partitioning_type,partition_count
2 From user_part_tables
3 where table_name='T_PARTITION_RANGE';
TABLE_NAME PARTITION PARTITION_COUNT
------------------------------ --------- ---------------
T_PARTITION_RANGE RANGE 5
复制merge的情况和官方文档描述一致，只要merge的时候，任意一个待合并的分区含有数据，合并后的分区对应的索引就会失效。
rowid 在merge合并后，都会发生变化。
drop
SQL> alter table T_PARTITION_RANGE drop partition T_RANGE_P6;
Table altered.
SQL> select index_name, partitioning_type, partition_count,LOCALITY,ALIGNMENT
2 From user_part_indexes
3 where index_name = 'IDX_PARTI_RANGE_ID';
INDEX_NAME PARTITION PARTITION_COUNT LOCALI ALIGNMENT
------------------------------ --------- --------------- ------ ------------
IDX_PARTI_RANGE_ID RANGE 4 GLOBAL PREFIXED
SQL> select partition_name,high_value,tablespace_name,status from user_ind_partitions
2 where index_name='IDX_PARTI_RANGE_ID';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME STATUS
------------------------------ ---------- ------------------------------ --------
I_RANGE_P1 10 ZHUO UNUSABLE
I_RANGE_P2 20 ZHUO UNUSABLE
I_RANGE_P3 30 ZHUO UNUSABLE
I_RANGE_PMAX MAXVALUE ZHUO UNUSABLE
SQL> select table_name,partitioning_type,partition_count
2 From user_part_tables
3 where table_name='T_PARTITION_RANGE';
TABLE_NAME PARTITION PARTITION_COUNT
------------------------------ --------- ---------------
T_PARTITION_RANGE RANGE 4
SQL>
SQL> select partition_name,high_value,tablespace_name
2 from user_tab_partitions
3 where table_name='T_PARTITION_RANGE'
4 order by partition_position;
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------- ------------------------------
T_RANGE_P1 10 USERS
T_RANGE_P8 20 USERS
T_RANGE_P9 30 USERS
T_RANGE_PMAX MAXVALUE USERS
复制SQL> alter table T_PARTITION_RANGE drop partition T_RANGE_P8 update indexes;
Table altered.
SQL> select index_name, partitioning_type, partition_count,LOCALITY,ALIGNMENT
2 From user_part_indexes
3 where index_name = 'IDX_PARTI_RANGE_ID';
INDEX_NAME PARTITION PARTITION_COUNT LOCALI ALIGNMENT
------------------------------ --------- --------------- ------ ------------
IDX_PARTI_RANGE_ID RANGE 4 GLOBAL PREFIXED
SQL> select partition_name,high_value,tablespace_name,status from user_ind_partitions
2 where index_name='IDX_PARTI_RANGE_ID';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME STATUS
------------------------------ ---------- ------------------------------ --------
I_RANGE_P1 10 ZHUO USABLE
I_RANGE_P2 20 ZHUO USABLE
I_RANGE_P3 30 ZHUO USABLE
I_RANGE_PMAX MAXVALUE ZHUO USABLE
复制如果你在执行该语句时没有指定update indexes 子句，也会导致global 索引的失效.导致所有的global分区索引所有分区都失效
add
SQL> alter table T_PARTITION_RANGE add partition T_RANGE_P2 values less than(20);
Table altered.
SQL> select table_name,partitioning_type,partition_count
2 From user_part_tables
3 where table_name='T_PARTITION_RANGE';
TABLE_NAME PARTITION PARTITION_COUNT
------------------------------ --------- ---------------
T_PARTITION_RANGE RANGE 2
SQL> select partition_name,high_value,tablespace_name
2 from user_tab_partitions
3 where table_name='T_PARTITION_RANGE'
4 order by partition_position;
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------- ------------------------------
T_RANGE_P1 10 USERS
T_RANGE_P2 20 USERS
SQL> select index_name, partitioning_type, partition_count,LOCALITY,ALIGNMENT
2 From user_part_indexes
3 where index_name = 'IDX_PARTI_RANGE_ID';
INDEX_NAME PARTITION PARTITION_COUNT LOCALI ALIGNMENT
------------------------------ --------- --------------- ------ ------------
IDX_PARTI_RANGE_ID RANGE 4 GLOBAL PREFIXED
SQL> select partition_name,high_value,tablespace_name,status from user_ind_partitions
2 where index_name='IDX_PARTI_RANGE_ID';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME STATUS
------------------------------ ---------- ------------------------------ --------
I_RANGE_P1 10 ZHUO USABLE
I_RANGE_P2 20 ZHUO USABLE
I_RANGE_P3 30 ZHUO USABLE
I_RANGE_PMAX MAXVALUE ZHUO USABLE
复制如官方文档所说，add分区，不会使range分区对应的global和local索引失效，都是有效的。
truncate
SQL> select id,name,rowid from t_partition_range;
ID NAME ROWID
---------- -------------------------------------------------- ------------------
4 ddd AAATynAAEAAACErAAA
4 ddd AAATynAAEAAACErAAB
13 asdewr AAATzMAAEAAAAErAAA
22 dasd AAATzNAAEAAAAUrAAA
SQL> select partition_name,high_value,tablespace_name,status from user_ind_partitions
2 where index_name='IDX_PARTI_RANGE_ID';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME STATUS
------------------------------ ---------- ------------------------------ --------
I_RANGE_P1 10 ZHUO USABLE
I_RANGE_P2 20 ZHUO USABLE
I_RANGE_P3 30 ZHUO USABLE
I_RANGE_PMAX MAXVALUE ZHUO USABLE
SQL> select partition_name,high_value,tablespace_name
2 from user_tab_partitions
3 where table_name='T_PARTITION_RANGE'
4 order by partition_position;
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------- ------------------------------
T_RANGE_P1 10 USERS
T_RANGE_P2 20 USERS
T_RANGE_P3 30 USERS
SQL> select * from T_PARTITION_RANGE partition (T_RANGE_P3);
ID NAME
---------- --------------------------------------------------
22 dasd
SQL> alter table T_PARTITION_RANGE truncate partition T_RANGE_P3;
Table truncated.
SQL> select index_name, partitioning_type, partition_count,LOCALITY,ALIGNMENT
2 From user_part_indexes
3 where index_name = 'IDX_PARTI_RANGE_ID';
INDEX_NAME PARTITION PARTITION_COUNT LOCALI ALIGNMENT
------------------------------ --------- --------------- ------ ------------
IDX_PARTI_RANGE_ID RANGE 4 GLOBAL PREFIXED
SQL> select partition_name,high_value,tablespace_name,status from user_ind_partitions
2 where index_name='IDX_PARTI_RANGE_ID';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME STATUS
------------------------------ ---------- ------------------------------ --------
I_RANGE_P1 10 ZHUO UNUSABLE
I_RANGE_P2 20 ZHUO UNUSABLE
I_RANGE_P3 30 ZHUO UNUSABLE
I_RANGE_PMAX MAXVALUE ZHUO UNUSABLE
复制truncate 分区，会使global索引失效，除非加update indexes子句。
move
SQL> select * from T_PARTITION_RANGE partition (T_RANGE_P3);
ID NAME
---------- --------------------------------------------------
22 dasd
SQL> alter table T_PARTITION_RANGE move partition T_RANGE_P1 tablespace zhuo;
Table altered.
SQL> select index_name, partitioning_type, partition_count,LOCALITY,ALIGNMENT
2 From user_part_indexes
3 where index_name = 'IDX_PARTI_RANGE_ID';
INDEX_NAME PARTITION PARTITION_COUNT LOCALI ALIGNMENT
------------------------------ --------- --------------- ------ ------------
IDX_PARTI_RANGE_ID RANGE 4 GLOBAL PREFIXED
SQL> select partition_name,high_value,tablespace_name,status from user_ind_partitions
2 where index_name='IDX_PARTI_RANGE_ID';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME STATUS
------------------------------ ---------- ------------------------------ --------
I_RANGE_P1 10 ZHUO UNUSABLE
I_RANGE_P2 20 ZHUO UNUSABLE
I_RANGE_P3 30 ZHUO UNUSABLE
I_RANGE_PMAX MAXVALUE ZHUO UNUSABLE
复制move操作会使global索引失效。
rebuild
global 索引不支持list分区，所以rebuild也不支持。
10g以后，全局分区索引支持hash了。
SQL> create index dx_parti_list_id on t_partition_list(id) global
2 partition by list(id)(
3 partition t_list_p1 values (1,2,3,4,5,6,7,8,9) tablespace zhuo,
4 partition t_list_p2 values (10,11,12,13,14,15,16,17,18,19) tablespace zhuo,
5 partition t_list_p3 values (20,21,22,23,24,25,26,27,28,29) tablespace zhuo,
6 partition t_list_pd values (default) tablespace zhuo);
partition by list(id)(
*
ERROR at line 2:
ORA-14151: invalid table partitioning method
复制碰上索引分区无效也很常见，比如分区表操作时未指定update indexes 子句就极有可能造成索引分区的无效，一般情况下，你都可以通过：Alter index idxname rebuild partition/subpartition ptname;重新编译。注意global 索引只支持range 分区和hash分区，local 索引无限制。
所以一般rebuild global index，都是range和hash。方法如下：
分区索引不能够将其作为整体重建，必须对每个分区重建
SQL> alter index IDX_PARTI_RANGE_ID rebuild;
alter index IDX_PARTI_RANGE_ID rebuild
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole
复制SQL> alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_P2;
Index altered.
SQL> alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_P3;
Index altered.
SQL> alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_PMAX;
Index altered.
复制总结如下：
alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_PMAX parallel 6 nologging online;
alter index IDX_PARTI_RANGE_ID noparallel;
如何对全局分区索引进行维护？
如前所述，当表分区发生表分区删除、合并、分离等维护操作之后，全局分区索引将失效，索引状态变为UNUSABLE。为此，Oracle提供了三种方式进行全局分区索引的重建并恢复为可用（USABLE）状态，以下以分区删除操作为例，介绍具体细节：
第一， 表分区删除之后，按全局分区索引的分区直接重建全局分区索引。例如：
alter table T_PARTITION_RANGE drop partition T_RANGE_P6;
alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_P1;
alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_P2;
alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_P3;
alter index IDX_PARTI_RANGE_ID rebuild partition I_RANGE_PMAX;
复制在这种方式下，表分区删除之后，全局分区索引将长时间处于不可用状态，一直到全局分区索引全部重建完成之后，才恢复为正常的可用状态（USABLE）。
第二， 先用DELETE语句删除相关分区的数据，然后再删除表分区。例如：
SQL> select * from t_partition_range partition (T_RANGE_P3);
ID NAME
---------- --------------------------------------------------
22 dasd
SQL> delete from t_partition_range partition(T_RANGE_P3);
1 row deleted.
SQL> commit;
Commit complete.
SQL> alter table t_partition_range drop partition(T_RANGE_P3);
Table altered.
SQL> select partition_name,high_value,tablespace_name,status from user_ind_partitions
2 where index_name='IDX_PARTI_RANGE_ID';
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME STATUS
------------------------------ ---------- ------------------------------ --------
I_RANGE_P1 10 ZHUO USABLE
I_RANGE_P2 20 ZHUO USABLE
I_RANGE_P3 30 ZHUO USABLE
I_RANGE_PMAX MAXVALUE ZHUO USABLE
SQL> select partition_name,high_value,tablespace_name
2 from user_tab_partitions
3 where table_name='T_PARTITION_RANGE'
4 order by partition_position;
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------- ------------------------------
T_RANGE_P1 10 ZHUO
T_RANGE_P2 20 USERS
复制在这种方式下，全局分区索引一直处于可用状态，但代价是DELETE操作不仅效率低，而且导致后台产生大量日志信息。我曾见过广东某银行客户就是采取了这种方式在删除分区并进行历史数据管理。
第三， 在删除表分区的同时，通过UPDATE INDEXES短语，同步维护全局分区索引。例如：
SQL> alter table T_PARTITION_RANGE drop partition T_RANGE_P8 update indexes;
复制在这种方式下，Oracle基于新的异步全局索引维护技术，确保全局分区索引一直处于可用状态，但表分区删除和索引重建操作的确需要消耗更长时间和更多资源。
尽管如此，我认为第三种方式依然是最佳技术，因为全局分区索引一直保持可用状态，而且资源消耗没有第二种方式大。但还是需要综合考虑不同业务场景、硬件环境、压力测试结果等方面情况，加以综合考量。
实际两种场景
1、在分区表上创建普通索引。
业内普遍没有采用全局分区索引一个重要原因是当表分区进行维护操作时，全局分区索引将失效，因此很多索引依然是普通索引。殊不知，普通索引也属于全局索引，也就是全局未分区索引（Global Nonpartitioned Index）。当表分区进行维护操作时，这些普通索引同样会变成不可用（UNUSABLE）状态，除非设计成本地分区索引。那与其这样，为什么不追求性能的极致，把一些重要的普通索引改造成全局分区索引呢？然后整体考虑全局索引的可维护性和可用性问题。
创建一个普通索引，也就是全局未分区索引：
SQL> drop index idx_parti_range_id;
Index dropped.
SQL> create index idx_parti_range_id on t_partition_range(id);
Index created.
SQL> select index_name, partitioning_type, partition_count,LOCALITY,ALIGNMENT
2 From user_part_indexes
3 where index_name = 'IDX_PARTI_RANGE_ID';
no rows selected
SQL> select partition_name,high_value,tablespace_name,status from user_ind_partitions
2 where index_name='IDX_PARTI_RANGE_ID';
no rows selected
SQL> select owner,index_name,index_type,table_owner,table_name,table_type,partitioned,status from dba_indexes where index_name in('IDX_PARTI_RANGE_ID');
OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE PAR STATUS
------ ------------------------------ ---------- ------------------------------ ------------------------------ ----------- --- --------
SCOTT IDX_PARTI_RANGE_ID NORMAL SCOTT T_PARTITION_RANGE TABLE NO VALID
复制对分区的维护，也会导致全局未分区索引（普通索引）失效：
SQL> select * from t_partition_range partition (T_RANGE_P2);
ID NAME
---------- --------------------------------------------------
13 asdewr
SQL> alter table T_PARTITION_RANGE truncate partition T_RANGE_P2;
Table truncated.
SQL> select * from t_partition_range partition (T_RANGE_P2);
no rows selected
SQL> select owner,index_name,index_type,table_owner,table_name,table_type,partitioned,status from dba_indexes where index_name in('IDX_PARTI_RANGE_ID');
OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE PAR STATUS
------ ------------------------------ ---------- ------------------------------ ------------------------------ ----------- --- --------
SCOTT IDX_PARTI_RANGE_ID NORMAL SCOTT T_PARTITION_RANGE TABLE NO UNUSABLE
复制2、在分区表中，要创建local的主键或者唯一索引，索引字段必须包含分区字段。不然报错ORA-14039。不分分区字段是前缀还是非前缀。所以就有可能是前缀分区索引，也有可能是非前缀分区索引。
SQL> drop index idx_parti_range_id;
Index dropped.
SQL> create unique index idx_parti_range_id on t_partition_range(name) local;
create unique index idx_parti_range_id on t_partition_range(name) local
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
SQL> drop index idx_parti_range_id;
Index dropped.
SQL> create unique index idx_parti_range_id on t_partition_range(name,id) local;
Index created.
SQL> drop index idx_parti_range_id;
Index dropped.
SQL> create unique index idx_parti_range_id on t_partition_range(id,name) local;
Index created.
复制只要包含，分区字段在前在后都可以，具体看需求。通俗理解就是既然是唯一或主键索引，那么只有全局索引才能更好地确保这种唯一性，本地分区索引是很难实现或管理索引的唯一性的。
补充前缀分区索引和非前缀分区索引概念。
所谓本地前缀分区索引，是指分区字段就是索引字段或者是组合索引的前缀字段。
即本地前缀分区索引，是指分区字段不是索引字段或者不是组合索引的前缀字段。
SQL> drop index idx_parti_range_id;
Index dropped.
SQL> create unique index idx_parti_range_id on t_partition_range(id,name) local;
Index created.
SQL> select index_name, partitioning_type, partition_count,LOCALITY,ALIGNMENT
2 From user_part_indexes
3 where index_name = 'IDX_PARTI_RANGE_ID';
INDEX_NAME PARTITION PARTITION_COUNT LOCALI ALIGNMENT
------------------------------ --------- --------------- ------ ------------
IDX_PARTI_RANGE_ID RANGE 4 LOCAL PREFIXED
SQL> drop index idx_parti_range_id;
Index dropped.
SQL> create unique index idx_parti_range_id on t_partition_range(name,id) local;
Index created.
SQL> select index_name, partitioning_type, partition_count,LOCALITY,ALIGNMENT
2 From user_part_indexes
3 where index_name = 'IDX_PARTI_RANGE_ID';
INDEX_NAME PARTITION PARTITION_COUNT LOCALI ALIGNMENT
------------------------------ --------- --------------- ------ ------------
IDX_PARTI_RANGE_ID RANGE 4 LOCAL NON_PREFIXED
--不是前缀字段，所以是非前缀分区索引
SQL> drop index idx_parti_range_id;
Index dropped.
SQL> create unique index idx_parti_range_id on t_partition_range(id) local;
Index created.
SQL> select index_name, partitioning_type, partition_count,LOCALITY,ALIGNMENT
2 From user_part_indexes
3 where index_name = 'IDX_PARTI_RANGE_ID';
INDEX_NAME PARTITION PARTITION_COUNT LOCALI ALIGNMENT
------------------------------ --------- --------------- ------ ------------
IDX_PARTI_RANGE_ID RANGE 4 LOCAL PREFIXED
复制区分他两，2个条件选其一：1、分区字段就是索引字段；OR 2、分区字段是组合索引的前缀字段。
总结
对于global索引，它的失效情况和local不一样，明显区别就是只要有操作使它失效，就不是单个分区对应的索引失效，而是整个global索引对应的所有的分区都失效。
分区索引总结：
split table，表里面有数据，会导致对应分区的本地索引失效。全局索引所有的分区失效。
merge table，表里面有数据，会导致对应分区的本地索引失效。全局索引所有的分区失效。
drop partition，删除分区时对应的索引分区会被同时删除，但其它分区的local 索引不会受到影响。全局索引所有的分区失效。
add partition，索引失效只发生在hash分区的情况下。下面的range分区不影响数据分布，所以local和global都不影响。
truncate partition，清空分区，也会清空对应的本地索引数据，但是本地索引不会失效。global分区索引所有分区都失效。
move partition，会使本地和全局都失效。
参考
VLDB and Partitioning Guide
最后修改时间：2022-03-01 15:37:25
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
目录
