oracle 分区_83_Oracle 分区扩展-3-分区表的索引维护

今天写《Oracle分区扩展 – 3 》，主要介绍分区表的索引相关内容，比如表级分区操作对分区索引的影响等。
我的环境:
• OS : Oracle Enterprise Linux 5.5 64Bit
• DB Type : Oracle Restart
• DB Version : 11.2.0.3
我用到的脚本：
-- 查看索引状态
[oracle@maa3 ~]$ cat showstate.sql
column INDEX_NAME format a30
column PARTITION_NAME format a30
select index_name, null partition_name, status
from user_indexes
where table_name = upper('&TABLE_NAME')
and partitioned = 'NO'
union
select index_name, partition_name, status
from user_ind_partitions
where index_name in
(
select index_name from user_indexes
where table_name = upper('&TABLE_NAME')
)
order by 1,2,3
/
创建研究对象分区表：
sys@MAA> create tablespace ts1 datafile size 10M;
sys@MAA> create tablespace ts2 datafile size 10M;
sys@MAA> create tablespace ts3 datafile size 10M;
sys@MAA> create tablespace ts4 datafile size 10M;
sys@MAA> create tablespace ts5 datafile size 10M;
luocs@MAA> create table ts_ltb
2 (id number,
3 name varchar2(100),
4 type varchar2(100)
5 )
6 partition by range(id)
7 (
8 partition p_01 values less than(500) tablespace ts1,
9 partition p_02 values less than(1000) tablespace ts2,
10 partition p_o3 values less than(1500) tablespace ts3,
11 partition p_04 values less than(2000) tablespace ts4,
12 partition p_05 values less than(maxvalue) tablespace ts5
13 )
14 /
Table created.
sys@MAA> insert into luocs.ts_ltb select object_id, object_name, object_type from dba_objects;
22989 rows created.
sys@MAA> commit;
Commit complete.
sys@MAA> create tablespace ts01 datafile size 10M;
sys@MAA> create tablespace ts02 datafile size 10M;
sys@MAA> create tablespace ts03 datafile size 10M;
sys@MAA> create tablespace ts04 datafile size 10M;
sys@MAA> create tablespace ts05 datafile size 10M;
sys@MAA> create tablespace ts06 datafile size 10M;
sys@MAA> create tablespace ts07 datafile size 10M;
sys@MAA> create tablespace ts08 datafile size 10M;
sys@MAA> create tablespace ts09 datafile size 10M;
sys@MAA> create tablespace ts10 datafile size 10M;
sys@MAA> create tablespace ts11 datafile size 10M;
sys@MAA> create tablespace ts12 datafile size 10M;
sys@MAA> create tablespace ts13 datafile size 10M;
创建索引：
-- CREATE THE LOCAL INDEXES
luocs@MAA> create index inx_ts_ltb_local
2 on ts_ltb(id)
3 local
4 (
5 partition pl_01 tablespace ts01,
6 partition pl_02 tablespace ts02,
7 partition pl_03 tablespace ts03,
8 partition pl_04 tablespace ts04,
9 partition pl_05 tablespace ts05
10 )
11 /
Index created.
-- CREATE THE GLOBAL INDEXES
luocs@MAA> create index inx_ts_ltb_global
2 on ts_ltb(name)
3 global
4 partition by hash(name)
5 partitions 5
6 store in (ts06,ts07,ts08,ts09,ts10)
7 /
Index created.
-- CREATE THE B*Tree INDEXES
luocs@MAA> create index inx_ts_ltb on ts_ltb(type) tablespace ts11;
Index created.
查看索引创建信息：
luocs@MAA> col INDEX_NAME for a30
luocs@MAA> col INDEX_TYPE for a30
luocs@MAA> select index_name, index_type, partitioned, status from user_indexes where table_name='TS_LTB';
INDEX_NAME INDEX_TYPE PARTIT STATUS
------------------------------ ------------------------------ ------ ----------------
INX_TS_LTB NORMAL NO VALID
INX_TS_LTB_GLOBAL NORMAL YES N/A
INX_TS_LTB_LOCAL NORMAL YES N/A
luocs@MAA> exec dbms_stats.gather_table_stats(user,'ts_ltb',cascade=>true);
PL/SQL procedure successfully completed.
luocs@MAA> SELECT PARTITION_NAME, TABLESPACE_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'TS_LTB';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P_01 TS1
P_02 TS2
P_O3 TS3
P_04 TS4
P_05 TS5
luocs@MAA> SELECT TABLE_NAME, PARTITION_NAME, NUM_ROWS FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TS_LTB';
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
TS_LTB P_01 497
TS_LTB P_02 500
TS_LTB P_04 500
TS_LTB P_05 20992
TS_LTB P_O3 500
比较表级别分区操作的索引分区维护
表级分区操作 非分区索引 本地分区索引 全局分区索引
增加分区 索引不受影响 索引不受影响 索引不受影响
拆分分区 整个索引被标记为UNUSABLE 受拆分操作影响的分区上的索引被标记为UNUSABLE 索引的所有分区都被标记为UNUSABLE
移动分区 整个索引被标记为UNUSABLE 被移动的分区上的索引被标记为UNUSABLE 索引的所有分区都被标记为UNUSABLE
交换分区 整个索引被标记为UNUSABLE 被交换的分区上的索引被标记为UNUSABLE 索引的所有分区都被标记为UNUSABLE
合并分区 整个索引被标记为UNUSABLE 受合并操作影响的分区上的索引被标记为UNUSABLE 索引的所有分区都被标记为UNUSABLE
截断分区 整个索引被标记为UNUSABLE 索引不受影响 索引的所有分区都被标记为UNUSABLE
删除分区 整个索引被标记为UNUSABLE 本地索引分区被删除，其余索引分区不受影响 索引的所有分区都被标记为UNUSABLE
令数据只读 不可能实现，除非整个表示静态的 通过表空间隔离可以令分区级别索引数据只读 理论上可以令分区级别索引数据只读。
(表上没有DML操作) 实际上无法实现，除非整个表是静态的
查看当前状态，这里使用脚本showstate：
luocs@MAA> @showstate
Enter value for table_name: ts_ltb
old 3: where table_name = upper('&TABLE_NAME')
new 3: where table_name = upper('ts_ltb')
Enter value for table_name: ts_ltb
old 11: where table_name = upper('&TABLE_NAME')
new 11: where table_name = upper('ts_ltb')
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INX_TS_LTB VALID
INX_TS_LTB_GLOBAL SYS_P61 USABLE
INX_TS_LTB_GLOBAL SYS_P62 USABLE
INX_TS_LTB_GLOBAL SYS_P63 USABLE
INX_TS_LTB_GLOBAL SYS_P64 USABLE
INX_TS_LTB_GLOBAL SYS_P65 USABLE
INX_TS_LTB_LOCAL PL_01 USABLE
INX_TS_LTB_LOCAL PL_02 USABLE
INX_TS_LTB_LOCAL PL_03 USABLE
INX_TS_LTB_LOCAL PL_04 USABLE
INX_TS_LTB_LOCAL PL_05 USABLE
11 rows selected.
1、添加分区(Add Partition)
因为我在创建分区表时指定了maxvalue，在添加新的分区时报错ORA-14074。
luocs@MAA> alter table ts_ltb add partition p_06 values less than(2500) tablespace ts6;
alter table ts_ltb add partition p_06 values less than(2500) tablespace ts6
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
这个问题可以通过split partition方法来解决
luocs@MAA> alter table ts_ltb split partition p_05 at (23899) into (partition p_06,partition p_05) update indexes;
Table altered.
luocs@MAA> alter table ts_ltb drop partition p_05 update indexes;
Table altered.
luocs@MAA> exec dbms_stats.gather_table_stats(user,'ts_ltb',cascade=>true);
PL/SQL procedure successfully completed.
luocs@MAA> SELECT PARTITION_NAME, TABLESPACE_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'TS_LTB';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------------------------------------
P_01 TS1
P_02 TS2
P_O3 TS3
P_04 TS4
P_06 TS5
luocs@MAA> SELECT TABLE_NAME, PARTITION_NAME, NUM_ROWS FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TS_LTB';
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
TS_LTB P_01 497
TS_LTB P_02 500
TS_LTB P_04 500
TS_LTB P_06 20991
TS_LTB P_O3 500
-- 以上问题解决之后
luocs@MAA> @showstate
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INX_TS_LTB VALID
INX_TS_LTB_GLOBAL SYS_P66 USABLE
INX_TS_LTB_GLOBAL SYS_P67 USABLE
INX_TS_LTB_GLOBAL SYS_P68 USABLE
INX_TS_LTB_GLOBAL SYS_P69 USABLE
INX_TS_LTB_GLOBAL SYS_P70 USABLE
INX_TS_LTB_LOCAL PL_01 USABLE
INX_TS_LTB_LOCAL PL_02 USABLE
INX_TS_LTB_LOCAL PL_03 USABLE
INX_TS_LTB_LOCAL PL_04 USABLE
INX_TS_LTB_LOCAL PL_05 USABLE
11 rows selected.
– 开始添加分区
luocs@MAA> alter table ts_ltb add partition p_05 values less than(30000) tablespace ts5;
Table altered.
luocs@MAA> @showstate
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INX_TS_LTB VALID
INX_TS_LTB_GLOBAL SYS_P66 USABLE
INX_TS_LTB_GLOBAL SYS_P67 USABLE
INX_TS_LTB_GLOBAL SYS_P68 USABLE
INX_TS_LTB_GLOBAL SYS_P69 USABLE
INX_TS_LTB_GLOBAL SYS_P70 USABLE
INX_TS_LTB_LOCAL PL_01 USABLE
INX_TS_LTB_LOCAL PL_02 USABLE
INX_TS_LTB_LOCAL PL_03 USABLE
INX_TS_LTB_LOCAL PL_04 USABLE
INX_TS_LTB_LOCAL PL_05 USABLE
INX_TS_LTB_LOCAL P_05 USABLE
12 rows selected.
结论：添加分区，任何现存的索引都不会受到影响
2、拆分分区(Split Partition)
上面为了解决问题，已经演示过了Split Partition操作了，下面重复一下：
luocs@MAA> alter table ts_ltb split partition p_06 at(3000) into (partition p_055 tablespace ts5, partition p_06 tablespace ts6);
Table altered.
luocs@MAA> SELECT PARTITION_NAME, TABLESPACE_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'TS_LTB';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------------------------------------
P_01 TS1
P_02 TS2
P_O3 TS3
P_04 TS4
P_06 TS6
P_055 TS5
6 rows selected.
luocs@MAA> exec dbms_stats.gather_table_stats(user,'ts_ltb',cascade=>true);
PL/SQL procedure successfully completed.
luocs@MAA> SELECT TABLE_NAME, PARTITION_NAME, NUM_ROWS FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TS_LTB';
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
TS_LTB P_01 497
TS_LTB P_02 500
TS_LTB P_04 500
TS_LTB P_05 0
TS_LTB P_055 1000
TS_LTB P_06 19991
TS_LTB P_O3 500
7 rows selected.
luocs@MAA> @showstate
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INX_TS_LTB UNUSABLE
INX_TS_LTB_GLOBAL SYS_P66 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P67 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P68 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P69 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P70 UNUSABLE
INX_TS_LTB_LOCAL PL_01 USABLE
INX_TS_LTB_LOCAL PL_02 USABLE
INX_TS_LTB_LOCAL PL_03 USABLE
INX_TS_LTB_LOCAL PL_04 USABLE
INX_TS_LTB_LOCAL PL_05 UNUSABLE
INX_TS_LTB_LOCAL P_05 USABLE
INX_TS_LTB_LOCAL P_055 UNUSABLE
13 rows selected.
结论：非分区索引受到影响，全局分区索引的所有分区都被标记为UNUSABLE，受拆分操作影响的本地分区上的索引被标记为UNUSABLE。
恢复索引为可用
重建非分区索引：
luocs@MAA> alter index INX_TS_LTB rebuild;
Index altered.
重建本地分区索引(受影响的)：
luocs@MAA> alter table ts_ltb modify partition p_06 rebuild unusable local indexes;
Table altered.
luocs@MAA> alter table ts_ltb modify partition p_055 rebuild unusable local indexes;
Table altered.
重建全局分区索引：
luocs@MAA> drop index INX_TS_LTB_GLOBAL;
Index dropped.
luocs@MAA> create index inx_ts_ltb_global
2 on ts_ltb(name)
3 global
4 partition by hash(name)
5 partitions 5
6 store in (ts06,ts07,ts08,ts09,ts10)
7 /
Index created.
因此恢复状态完毕
luocs@MAA> @showstate
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INX_TS_LTB VALID
INX_TS_LTB_GLOBAL SYS_P71 USABLE
INX_TS_LTB_GLOBAL SYS_P72 USABLE
INX_TS_LTB_GLOBAL SYS_P73 USABLE
INX_TS_LTB_GLOBAL SYS_P74 USABLE
INX_TS_LTB_GLOBAL SYS_P75 USABLE
INX_TS_LTB_LOCAL PL_01 USABLE
INX_TS_LTB_LOCAL PL_02 USABLE
INX_TS_LTB_LOCAL PL_03 USABLE
INX_TS_LTB_LOCAL PL_04 USABLE
INX_TS_LTB_LOCAL PL_05 USABLE
INX_TS_LTB_LOCAL P_05 USABLE
INX_TS_LTB_LOCAL P_055 USABLE
13 rows selected.
3、移动分区(Move Partition)
luocs@MAA> alter table ts_ltb move partition p_02 tablespace ts8;
Table altered.
luocs@MAA> SELECT PARTITION_NAME, TABLESPACE_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'TS_LTB';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------------------------------------
P_01 TS1
P_O3 TS3
P_04 TS4
P_06 TS6
P_055 TS5
P_02 TS8
6 rows selected.
luocs@MAA> @showstate
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INX_TS_LTB UNUSABLE
INX_TS_LTB_GLOBAL SYS_P71 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P72 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P73 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P74 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P75 UNUSABLE
INX_TS_LTB_LOCAL PL_01 USABLE
INX_TS_LTB_LOCAL PL_02 UNUSABLE
INX_TS_LTB_LOCAL PL_03 USABLE
INX_TS_LTB_LOCAL PL_04 USABLE
INX_TS_LTB_LOCAL PL_05 USABLE
INX_TS_LTB_LOCAL P_05 USABLE
INX_TS_LTB_LOCAL P_055 USABLE
结论：非分区索引受到影响，全局分区索引的所有分区都被标记为UNUSABLE，被移动的分区上的索引被标记为UNUSABLE。
恢复索引为可用
luocs@MAA> alter index INX_TS_LTB rebuild;
Index altered.
luocs@MAA> alter table ts_ltb modify partition p_02 rebuild unusable local indexes;
Table altered.
luocs@MAA> drop index INX_TS_LTB_GLOBAL;
Index dropped.
luocs@MAA> create index inx_ts_ltb_global
2 on ts_ltb(name)
3 global
4 partition by hash(name)
5 partitions 5
6 store in (ts06,ts07,ts08,ts09,ts10)
7 /
Index created.
恢复索引状态完毕
luocs@MAA> @showstate
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INX_TS_LTB VALID
INX_TS_LTB_GLOBAL SYS_P76 USABLE
INX_TS_LTB_GLOBAL SYS_P77 USABLE
INX_TS_LTB_GLOBAL SYS_P78 USABLE
INX_TS_LTB_GLOBAL SYS_P79 USABLE
INX_TS_LTB_GLOBAL SYS_P80 USABLE
INX_TS_LTB_LOCAL PL_01 USABLE
INX_TS_LTB_LOCAL PL_02 USABLE
INX_TS_LTB_LOCAL PL_03 USABLE
INX_TS_LTB_LOCAL PL_04 USABLE
INX_TS_LTB_LOCAL PL_05 USABLE
INX_TS_LTB_LOCAL P_05 USABLE
INX_TS_LTB_LOCAL P_055 USABLE
13 rows selected.
4、交换分区(Exchange Partition)
创建一个临时表
luocs@MAA> create table ts_ltb_temp as select * from ts_ltb where 0=1;
Table created.
luocs@MAA> alter table ts_ltb exchange partition p_01 with table ts_ltb_temp;
Table altered.
luocs@MAA> select count(*) from ts_ltb partition(p_01);
COUNT(*)
----------
0
luocs@MAA> select count(*) from ts_ltb_temp;
COUNT(*)
----------
497
luocs@MAA> @showstate
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INX_TS_LTB UNUSABLE
INX_TS_LTB_GLOBAL SYS_P76 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P77 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P78 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P79 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P80 UNUSABLE
INX_TS_LTB_LOCAL PL_01 UNUSABLE
INX_TS_LTB_LOCAL PL_02 USABLE
INX_TS_LTB_LOCAL PL_03 USABLE
INX_TS_LTB_LOCAL PL_04 USABLE
INX_TS_LTB_LOCAL PL_05 USABLE
INX_TS_LTB_LOCAL P_05 USABLE
INX_TS_LTB_LOCAL P_055 USABLE
13 rows selected.
结论：非分区索引受到影响，全局分区索引的所有分区都被标记为UNUSABLE，被交换的分区上的索引被标记为UNUSABLE。
恢复索引为可用
luocs@MAA> alter index INX_TS_LTB rebuild;
Index altered.
luocs@MAA> alter table ts_ltb modify partition p_01 rebuild unusable local indexes;
Table altered.
luocs@MAA> drop index INX_TS_LTB_GLOBAL;
Index dropped.
luocs@MAA> create index inx_ts_ltb_global
2 on ts_ltb(name)
3 global
4 partition by hash(name)
5 partitions 5
6 store in (ts06,ts07,ts08,ts09,ts10)
7 /
Index created.
恢复索引状态完毕
luocs@MAA> @showstate
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INX_TS_LTB VALID
INX_TS_LTB_GLOBAL SYS_P81 USABLE
INX_TS_LTB_GLOBAL SYS_P82 USABLE
INX_TS_LTB_GLOBAL SYS_P83 USABLE
INX_TS_LTB_GLOBAL SYS_P84 USABLE
INX_TS_LTB_GLOBAL SYS_P85 USABLE
INX_TS_LTB_LOCAL PL_01 USABLE
INX_TS_LTB_LOCAL PL_02 USABLE
INX_TS_LTB_LOCAL PL_03 USABLE
INX_TS_LTB_LOCAL PL_04 USABLE
INX_TS_LTB_LOCAL PL_05 USABLE
INX_TS_LTB_LOCAL P_05 USABLE
INX_TS_LTB_LOCAL P_055 USABLE
13 rows selected.
5、合并分区(Merge Partition)
luocs@MAA> alter table ts_ltb merge partitions p_01,p_02 into partition p_02;
Table altered.
-- 如果不指定表空间，默认会存放user表空间，如下
luocs@MAA> SELECT PARTITION_NAME, TABLESPACE_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'TS_LTB';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------------------------------------
P_O3 TS3
P_04 TS4
P_06 TS6
P_055 TS5
P_02 USERS
luocs@MAA> @showstate
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INX_TS_LTB UNUSABLE
INX_TS_LTB_GLOBAL SYS_P81 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P82 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P83 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P84 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P85 UNUSABLE
INX_TS_LTB_LOCAL PL_02 UNUSABLE
INX_TS_LTB_LOCAL PL_03 USABLE
INX_TS_LTB_LOCAL PL_04 USABLE
INX_TS_LTB_LOCAL PL_05 USABLE
INX_TS_LTB_LOCAL P_05 USABLE
INX_TS_LTB_LOCAL P_055 USABLE
12 rows selected.
结论：非分区索引受到影响，全局分区索引的所有分区都被标记为UNUSABLE，受合并操作影响的分区上的索引被标记为UNUSABLE。
恢复索引为可用
luocs@MAA> alter index INX_TS_LTB rebuild;
Index altered.
luocs@MAA> alter table ts_ltb modify partition p_02 rebuild unusable local indexes;
Table altered.
luocs@MAA> drop index INX_TS_LTB_GLOBAL;
Index dropped.
luocs@MAA> create index inx_ts_ltb_global
2 on ts_ltb(name)
3 global
4 partition by hash(name)
5 partitions 5
6 store in (ts06,ts07,ts08,ts09,ts10)
7 /
Index created.
恢复索引状态完毕
luocs@MAA> @showstate
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INX_TS_LTB VALID
INX_TS_LTB_GLOBAL SYS_P86 USABLE
INX_TS_LTB_GLOBAL SYS_P87 USABLE
INX_TS_LTB_GLOBAL SYS_P88 USABLE
INX_TS_LTB_GLOBAL SYS_P89 USABLE
INX_TS_LTB_GLOBAL SYS_P90 USABLE
INX_TS_LTB_LOCAL PL_02 USABLE
INX_TS_LTB_LOCAL PL_03 USABLE
INX_TS_LTB_LOCAL PL_04 USABLE
INX_TS_LTB_LOCAL PL_05 USABLE
INX_TS_LTB_LOCAL P_05 USABLE
INX_TS_LTB_LOCAL P_055 USABLE
12 rows selected.
6、截断分区(Truncate Partition)
luocs@MAA> alter table ts_ltb truncate partition p_055;
Table truncated.
luocs@MAA> @showstate
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INX_TS_LTB UNUSABLE
INX_TS_LTB_GLOBAL SYS_P86 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P87 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P88 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P89 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P90 UNUSABLE
INX_TS_LTB_LOCAL PL_02 USABLE
INX_TS_LTB_LOCAL PL_03 USABLE
INX_TS_LTB_LOCAL PL_04 USABLE
INX_TS_LTB_LOCAL PL_05 USABLE
INX_TS_LTB_LOCAL P_05 USABLE
INX_TS_LTB_LOCAL P_055 USABLE
12 rows selected.
结论：非分区索引受到影响，全局分区索引的所有分区都被标记为UNUSABLE，本地分区索引不受影响。
恢复索引为可用
luocs@MAA> alter index INX_TS_LTB rebuild;
Index altered.
luocs@MAA> drop index INX_TS_LTB_GLOBAL;
Index dropped.
luocs@MAA> create index inx_ts_ltb_global
2 on ts_ltb(name)
3 global
4 partition by hash(name)
5 partitions 5
6 store in (ts06,ts07,ts08,ts09,ts10)
7 /
Index created.
恢复索引状态完毕
luocs@MAA> @showstate
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INX_TS_LTB VALID
INX_TS_LTB_GLOBAL SYS_P91 USABLE
INX_TS_LTB_GLOBAL SYS_P92 USABLE
INX_TS_LTB_GLOBAL SYS_P93 USABLE
INX_TS_LTB_GLOBAL SYS_P94 USABLE
INX_TS_LTB_GLOBAL SYS_P95 USABLE
INX_TS_LTB_LOCAL PL_02 USABLE
INX_TS_LTB_LOCAL PL_03 USABLE
INX_TS_LTB_LOCAL PL_04 USABLE
INX_TS_LTB_LOCAL PL_05 USABLE
INX_TS_LTB_LOCAL P_05 USABLE
INX_TS_LTB_LOCAL P_055 USABLE
12 rows selected.
7、删除分区(Drop Partitions)
– 删除没有任何数据的分区
luocs@MAA> alter table ts_ltb drop partition p_055;
Table altered.
luocs@MAA> @showstate
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INX_TS_LTB VALID
INX_TS_LTB_GLOBAL SYS_P91 USABLE
INX_TS_LTB_GLOBAL SYS_P92 USABLE
INX_TS_LTB_GLOBAL SYS_P93 USABLE
INX_TS_LTB_GLOBAL SYS_P94 USABLE
INX_TS_LTB_GLOBAL SYS_P95 USABLE
INX_TS_LTB_LOCAL PL_02 USABLE
INX_TS_LTB_LOCAL PL_03 USABLE
INX_TS_LTB_LOCAL PL_04 USABLE
INX_TS_LTB_LOCAL PL_05 USABLE
INX_TS_LTB_LOCAL P_05 USABLE
11 rows selected.
结论：在要删除的分区上没有数据时，删除分区操作不会影响任何索引。
– 删除有数据的分区
luocs@MAA> alter table ts_ltb drop partition p_02;
Table altered.
luocs@MAA> @showstate
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INX_TS_LTB UNUSABLE
INX_TS_LTB_GLOBAL SYS_P91 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P92 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P93 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P94 UNUSABLE
INX_TS_LTB_GLOBAL SYS_P95 UNUSABLE
INX_TS_LTB_LOCAL PL_03 USABLE
INX_TS_LTB_LOCAL PL_04 USABLE
INX_TS_LTB_LOCAL PL_05 USABLE
INX_TS_LTB_LOCAL P_05 USABLE
10 rows selected.
结论：非分区索引受到影响，全局分区索引的所有分区都被标记为UNUSABLE，本地索引分区被删除，其余索引分区不受影响。
恢复索引为可用
luocs@MAA> alter index INX_TS_LTB rebuild;
Index altered.
luocs@MAA> drop index INX_TS_LTB_GLOBAL;
Index dropped.
luocs@MAA> create index inx_ts_ltb_global
2 on ts_ltb(name)
3 global
4 partition by hash(name)
5 partitions 5
6 store in (ts06,ts07,ts08,ts09,ts10)
7 /
Index created.
恢复索引状态完毕
luocs@MAA> @showstate
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INX_TS_LTB VALID
INX_TS_LTB_GLOBAL SYS_P100 USABLE
INX_TS_LTB_GLOBAL SYS_P96 USABLE
INX_TS_LTB_GLOBAL SYS_P97 USABLE
INX_TS_LTB_GLOBAL SYS_P98 USABLE
INX_TS_LTB_GLOBAL SYS_P99 USABLE
INX_TS_LTB_LOCAL PL_03 USABLE
INX_TS_LTB_LOCAL PL_04 USABLE
INX_TS_LTB_LOCAL PL_05 USABLE
INX_TS_LTB_LOCAL P_05 USABLE
10 rows selected.
在上面，我为演示表级分区操作对索引分区的影响，因此在表级分区操作之后都重建索引来恢复可用状态，
其实，Oracle提供表级分区操作的时候自动维护索引的方法，添加update indexes子句：
luocs@MAA> alter table ts_ltb move partition p_04 tablespace ts7 update indexes;
Table altered.
luocs@MAA> @showstate
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INX_TS_LTB VALID
INX_TS_LTB_GLOBAL SYS_P100 USABLE
INX_TS_LTB_GLOBAL SYS_P96 USABLE
INX_TS_LTB_GLOBAL SYS_P97 USABLE
INX_TS_LTB_GLOBAL SYS_P98 USABLE
INX_TS_LTB_GLOBAL SYS_P99 USABLE
INX_TS_LTB_LOCAL PL_03 USABLE
INX_TS_LTB_LOCAL PL_04 USABLE
INX_TS_LTB_LOCAL PL_05 USABLE
INX_TS_LTB_LOCAL P_05 USABLE
10 rows selected.
我在《Oracle 分区扩展 – 3 分区表的索引维护》一文最后提到使用update indexes子句以在执行分区维护操作时更新索引。
我在这里补充内容，介绍下update global indexes。
我们从语句中很容易看出，指定update global indexes子句，是在使用全局分区索引时，为防止其被标记为不可用的。在维护分区操作过程中全局分区索引保持在线和可用，并且之后也不必重建。
但添加分区操作对该语句有个要求：只能在添加一个分区到HASH分区表或HASH分区表的子分区时,指定update global indexes子句。
不能在RANGE分区表中添加分区时指定该子句。
如果向HASH分区表中添加一个分区时不指定update global indexes子句，那么索引就会变得不可用。
备注：在《Oracle 分区扩展 – 3 分区表的索引维护》一文里，我已经演示过在RANGE分区表中添加分区操作对索引的影响。
进行如下测试：
创建一个HASH分区表
luocs@MAA> create table ts_ltb_hash
2 (id number,
3 name varchar2(100),
4 type varchar2(100)
5 )
6 partition by hash(id)
7 (
8 partition ph_01,partition ph_02,partition ph_03,partition ph_04,partition ph_05
9 );
Table created.
luocs@MAA> insert into ts_ltb_hash select object_id, object_name, object_type from all_objects;
20962 rows created.
luocs@MAA> commit;
Commit complete.
luocs@MAA> create index inx_ts_ltb_hash_global on ts_ltb_hash(id)
2 global partition by range(id)
3 (partition pih_01 values less than(10000),
4 partition pih_02 values less than(maxvalue))
5 /
Index created.
luocs@MAA> create index inx_ts_ltb_hash_local on ts_ltb_hash(name)
2 local
3 partition by hash(name)
4 partitions 5
5 /
partition by hash(name)
*
ERROR at line 3:
ORA-02158: invalid CREATE INDEX option
luocs@MAA> create index inx_ts_ltb_hash_local on ts_ltb_hash(name) local;
Index created.
luocs@MAA> create index ins_ts_ltb_hash on ts_ltb_hash(type);
Index created.
luocs@MAA> @showstate
Enter value for table_name: ts_ltb_hash
old 3: where table_name = upper('&TABLE_NAME')
new 3: where table_name = upper('ts_ltb_hash')
Enter value for table_name: ts_ltb_hash
old 11: where table_name = upper('&TABLE_NAME')
new 11: where table_name = upper('ts_ltb_hash')
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INS_TS_LTB_HASH VALID
INX_TS_LTB_HASH_GLOBAL PIH_01 USABLE
INX_TS_LTB_HASH_GLOBAL PIH_02 USABLE
INX_TS_LTB_HASH_LOCAL PH_01 USABLE
INX_TS_LTB_HASH_LOCAL PH_02 USABLE
INX_TS_LTB_HASH_LOCAL PH_03 USABLE
INX_TS_LTB_HASH_LOCAL PH_04 USABLE
INX_TS_LTB_HASH_LOCAL PH_05 USABLE
8 rows selected.
-- 添加分区
luocs@MAA> alter table ts_ltb_hash add partition ph_06;
Table altered.
-- 下面可见添加分区操作之后索引变得不可用
luocs@MAA> @showstate
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INS_TS_LTB_HASH UNUSABLE
INX_TS_LTB_HASH_GLOBAL PIH_01 UNUSABLE
INX_TS_LTB_HASH_GLOBAL PIH_02 UNUSABLE
INX_TS_LTB_HASH_LOCAL PH_01 USABLE
INX_TS_LTB_HASH_LOCAL PH_02 UNUSABLE
INX_TS_LTB_HASH_LOCAL PH_03 USABLE
INX_TS_LTB_HASH_LOCAL PH_04 USABLE
INX_TS_LTB_HASH_LOCAL PH_05 USABLE
INX_TS_LTB_HASH_LOCAL PH_06 UNUSABLE
9 rows selected.
恢复索引状态
-- 重建全局分区索引
luocs@MAA> alter index INX_TS_LTB_HASH_GLOBAL rebuild partition PIH_01;
Index altered.
luocs@MAA> alter index INX_TS_LTB_HASH_GLOBAL rebuild partition PIH_02;
Index altered.
-- 重建受影响的本地分区索引
luocs@MAA> alter table ts_ltb_hash modify partition PH_02 rebuild unusable local indexes;
Table altered.
luocs@MAA> alter table ts_ltb_hash modify partition PH_06 rebuild unusable local indexes;
Table altered.
-- 重建普通索引
luocs@MAA> alter index INS_TS_LTB_HASH rebuild;
Index altered.
luocs@MAA> @showstate
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INS_TS_LTB_HASH VALID
INX_TS_LTB_HASH_GLOBAL PIH_01 USABLE
INX_TS_LTB_HASH_GLOBAL PIH_02 USABLE
INX_TS_LTB_HASH_LOCAL PH_01 USABLE
INX_TS_LTB_HASH_LOCAL PH_02 USABLE
INX_TS_LTB_HASH_LOCAL PH_03 USABLE
INX_TS_LTB_HASH_LOCAL PH_04 USABLE
INX_TS_LTB_HASH_LOCAL PH_05 USABLE
INX_TS_LTB_HASH_LOCAL PH_06 USABLE
9 rows selected.
但如果指定子句update global indexes，那么除了受影响的本地分区索引外都不受影响。
luocs@MAA> alter table ts_ltb_hash add partition ph_07 update global indexes;
Table altered.
luocs@MAA> @showstate
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ----------------
INS_TS_LTB_HASH VALID
INX_TS_LTB_HASH_GLOBAL PIH_01 USABLE
INX_TS_LTB_HASH_GLOBAL PIH_02 USABLE
INX_TS_LTB_HASH_LOCAL PH_01 USABLE
INX_TS_LTB_HASH_LOCAL PH_02 USABLE
INX_TS_LTB_HASH_LOCAL PH_03 UNUSABLE
INX_TS_LTB_HASH_LOCAL PH_04 USABLE
INX_TS_LTB_HASH_LOCAL PH_05 USABLE
INX_TS_LTB_HASH_LOCAL PH_06 USABLE
INX_TS_LTB_HASH_LOCAL PH_07 UNUSABLE
10 rows selected.
恢复索引状态方法上面已经介绍过，略！