。
案例
Step1.新建测试表，构造测试数据
我们这里建立一个list-hash的复合分区的测试表 ,同时为ARTISAN_ID这个字段创建了local索引,同时将test_primarykey_id 设置为主键。
-- Create tablecreate table GLOBAL_INDEX_PRIMARYKEY ( test_primarykey_id NUMBER(12) not null, artisan_id NUMBER(12) not null, created_date DATE not null, eff_date DATE not null, exp_date DATE, part_id NUMBER(6) default to_number(to_char(sysdate,'dd')) not null) partition by list (PART_ID) subpartition by hash (TEST_PRIMARYKEY_ID) ( partition P1 values (1) tablespace TAB_ARTISAN pctfree 10 initrans 1 maxtrans 255 ( subpartition P1_1 tablespace TAB_ARTISAN, subpartition P2_1 tablespace TAB_ARTISAN, subpartition P3_1 tablespace TAB_ARTISAN, subpartition P4_1 tablespace TAB_ARTISAN, subpartition P5_1 tablespace TAB_ARTISAN, subpartition P6_1 tablespace TAB_ARTISAN, subpartition P7_1 tablespace TAB_ARTISAN, subpartition P8_1 tablespace TAB_ARTISAN ), partition P2 values (2) tablespace TAB_ARTISAN pctfree 10 initrans 1 maxtrans 255 ( subpartition P1_2 tablespace TAB_ARTISAN, subpartition P2_2 tablespace TAB_ARTISAN, subpartition P3_2 tablespace TAB_ARTISAN, subpartition P4_2 tablespace TAB_ARTISAN, subpartition P5_2 tablespace TAB_ARTISAN, subpartition P6_2 tablespace TAB_ARTISAN, subpartition P7_2 tablespace TAB_ARTISAN, subpartition P8_2 tablespace TAB_ARTISAN ) );-- Create/Recreate indexes create index IDX_ARTISAN_ID on GLOBAL_INDEX_PRIMARYKEY (ARTISAN_ID) local;-- Create/Recreate primary, unique and foreign key constraints alter table GLOBAL_INDEX_PRIMARYKEY add constraint PK_GLOBAL_INDEX primary key (TEST_PRIMARYKEY_ID) using index tablespace TAB_ARTISAN pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );
PLSQL中查看
然后构造部分数据
select count(1) from GLOBAL_INDEX_PRIMARYKEY partition(p1) a ; -- 140select count(1) from GLOBAL_INDEX_PRIMARYKEY subpartition(p1_1) a ; --8select count(1) from GLOBAL_INDEX_PRIMARYKEY subpartition(p2_1) a ; -- 19select count(1) from GLOBAL_INDEX_PRIMARYKEY subpartition(p3_1) a ;-- 21select count(1) from GLOBAL_INDEX_PRIMARYKEY subpartition(p4_1) a ;-- 13select count(1) from GLOBAL_INDEX_PRIMARYKEY subpartition(p5_1) a ;-- 16select count(1) from GLOBAL_INDEX_PRIMARYKEY subpartition(p6_1) a ;-- 25select count(1) from GLOBAL_INDEX_PRIMARYKEY subpartition(p7_1) a ;-- 16select count(1) from GLOBAL_INDEX_PRIMARYKEY subpartition(p8_1) a ;-- 22select count(1) from GLOBAL_INDEX_PRIMARYKEY partition(p2) a ; -- 90select count(1) from GLOBAL_INDEX_PRIMARYKEY subpartition(p1_2) a ;--12select count(1) from GLOBAL_INDEX_PRIMARYKEY subpartition(p2_2) a ;--13select count(1) from GLOBAL_INDEX_PRIMARYKEY subpartition(p3_2) a ;--9select count(1) from GLOBAL_INDEX_PRIMARYKEY subpartition(p4_2) a ;--6select count(1) from GLOBAL_INDEX_PRIMARYKEY subpartition(p5_2) a ;--13select count(1) from GLOBAL_INDEX_PRIMARYKEY subpartition(p6_2) a ;--10select count(1) from GLOBAL_INDEX_PRIMARYKEY subpartition(p7_2) a ;--16select count(1) from GLOBAL_INDEX_PRIMARYKEY subpartition(p8_2) a ;--1
Step2. 查看索引状态
Step2.1 普通索引
索引 如果是N/A 继续查user_ind_partitions
SQL> select a.table_name ,a.index_name,a.status from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';TABLE_NAME INDEX_NAME STATUS ------------------------------ ------------------------------ -------- GLOBAL_INDEX_PRIMARYKEY PK_GLOBAL_INDEX VALID GLOBAL_INDEX_PRIMARYKEY IDX_ARTISAN_ID N/ASQL>
Step2.2 分区索引
分区索引 如果是N/A 继续查 user_ind_subpartitions
SQL> select a.partition_name, a.index_name, a.status 2 from user_ind_partitions a 3 where a.index_name in (select a.index_name 4 from user_indexes a 5 where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY');PARTITION_NAME INDEX_NAME STATUS ------------------------------ ------------------------------ -------- P1 IDX_ARTISAN_ID N/AP2 IDX_ARTISAN_ID N/ASQL>
Step2.3 子分区索引
子分区， 因为该表复合分区 ,所以应该可以在 user_ind_subpartitions 查看到 索引的状态 USABLE
SQL> select a.index_name, a.partition_name, a.subpartition_name, a.status 2 from user_ind_subpartitions a 3 where a.index_name in 4 (select a.index_name 5 from user_ind_partitions a 6 where a.index_name in 7 (select a.index_name 8 from user_indexes a 9 where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY')); INDEX_NAME PARTITION_NAME SUBPARTITION_NAME STATUS ------------------------------ ------------------------------ ------------------------------ -------- IDX_ARTISAN_ID P1 P1_1 USABLE IDX_ARTISAN_ID P1 P2_1 USABLE IDX_ARTISAN_ID P1 P3_1 USABLE IDX_ARTISAN_ID P1 P4_1 USABLE IDX_ARTISAN_ID P1 P5_1 USABLE IDX_ARTISAN_ID P1 P6_1 USABLE IDX_ARTISAN_ID P1 P7_1 USABLE IDX_ARTISAN_ID P1 P8_1 USABLE IDX_ARTISAN_ID P2 P1_2 USABLE IDX_ARTISAN_ID P2 P2_2 USABLE IDX_ARTISAN_ID P2 P3_2 USABLE IDX_ARTISAN_ID P2 P4_2 USABLE IDX_ARTISAN_ID P2 P5_2 USABLE IDX_ARTISAN_ID P2 P6_2 USABLE IDX_ARTISAN_ID P2 P7_2 USABLE IDX_ARTISAN_ID P2 P8_2 USABLE16 rows selected SQL>
Step3. 探究truncate/drop分区对global索引以及local索引的影响
Step3.1 不指定update global indexes的场景
导致全局索引失效，向表中写入数据失败，抛出 ORA-01502: index "ARTISAN.PK_GLOBAL_INDEX" or partition of such index is in unusable state 。local索引正常。需要重建global 索引
--- 1.1 140条数据 select count(1) from GLOBAL_INDEX_PRIMARYKEY partition(P1) a ; -- 140 -- 1.2 truncate 分区 不指定update global indexes的情况即不维护全局索引。 alter table GLOBAL_INDEX_PRIMARYKEY truncate partition P1 ;-- 1.3 全局索引 ---------------- 失效 UNUSABLE状态select a.table_name ,a.index_name,a.status from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';GLOBAL_INDEX_PRIMARYKEY PK_GLOBAL_INDEX UNUSABLE
GLOBAL_INDEX_PRIMARYKEY IDX_ARTISAN_ID N/A--1.4 local索引 ------------- OK select a.partition_name, a.index_name, a.status from user_ind_partitions a where a.index_name in
(select a.index_name from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY');select a.index_name, a.partition_name, a.subpartition_name, a.status from user_ind_subpartitions a where a.index_name in
(select a.index_name from user_ind_partitions a where a.index_name in
(select a.index_name from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY'));IDX_ARTISAN_ID P1 P1_1 USABLE
IDX_ARTISAN_ID P1 P2_1 USABLE
IDX_ARTISAN_ID P1 P3_1 USABLE
IDX_ARTISAN_ID P1 P4_1 USABLE
IDX_ARTISAN_ID P1 P5_1 USABLE
IDX_ARTISAN_ID P1 P6_1 USABLE
IDX_ARTISAN_ID P1 P7_1 USABLE
IDX_ARTISAN_ID P1 P8_1 USABLE
IDX_ARTISAN_ID P2 P1_2 USABLE
IDX_ARTISAN_ID P2 P2_2 USABLE
IDX_ARTISAN_ID P2 P3_2 USABLE
IDX_ARTISAN_ID P2 P4_2 USABLE
IDX_ARTISAN_ID P2 P5_2 USABLE
IDX_ARTISAN_ID P2 P6_2 USABLE
IDX_ARTISAN_ID P2 P7_2 USABLE
IDX_ARTISAN_ID P2 P8_2 USABLE--1.5 写入数据 ，此时抛出ORA-01502 异常，会影响业务。insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)values (8888, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 1);insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)values (9999, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 2);ORA-01502: index "ARTISAN.PK_GLOBAL_INDEX" or partition of such index is in unusable state
Cause: An attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation
Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition
-- 1.5 重建 全局索引
alter index PK_GLOBAL_INDEX rebuild online nologging;-- 1.6 重新查询 ，全局索引valid状态，OKselect table_name,index_name,status from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';GLOBAL_INDEX_PRIMARYKEY PK_GLOBAL_INDEX VALID
GLOBAL_INDEX_PRIMARYKEY IDX_ARTISAN_ID N/A-- 重新写入数据insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)values (8888, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 1);insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)values (9999, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 2);-- OK的. 回滚吧,暂时不写入表里.Step3.2指定update global indexes的场景
因为指定了update global indexes ，全局索引VALID ，local索引正常。 数据写入正常，不会影响业务。
------ 为了验证 维护全局索引的情况， P1被truncate掉了， 我们使用P2分区 --- 2.1 90 条数据 select count(1) from GLOBAL_INDEX_PRIMARYKEY partition(P2) a ; -- 90 --先查下全局索引的状态 VALID select a.table_name ,a.index_name,a.status from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';GLOBAL_INDEX_PRIMARYKEY PK_GLOBAL_INDEX VALID
GLOBAL_INDEX_PRIMARYKEY IDX_ARTISAN_ID N/A-- 2.2 truncate 分区 加 update global indexes的情况 alter table GLOBAL_INDEX_PRIMARYKEY truncate partition P2 update global indexes ; -- 2.3 全局索引 ---------------- 有效 VALIDselect a.table_name ,a.index_name,a.status from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';GLOBAL_INDEX_PRIMARYKEY PK_GLOBAL_INDEX VALID
GLOBAL_INDEX_PRIMARYKEY IDX_ARTISAN_ID N/A--2.4 local索引 ------------- OK select a.partition_name, a.index_name, a.status from user_ind_partitions a where a.index_name in
(select a.index_name from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY');select a.index_name, a.partition_name, a.subpartition_name, a.status from user_ind_subpartitions a where a.index_name in
(select a.index_name from user_ind_partitions a where a.index_name in
(select a.index_name from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY'));IDX_ARTISAN_ID P1 P1_1 USABLE
IDX_ARTISAN_ID P1 P2_1 USABLE
IDX_ARTISAN_ID P1 P3_1 USABLE
IDX_ARTISAN_ID P1 P4_1 USABLE
IDX_ARTISAN_ID P1 P5_1 USABLE
IDX_ARTISAN_ID P1 P6_1 USABLE
IDX_ARTISAN_ID P1 P7_1 USABLE
IDX_ARTISAN_ID P1 P8_1 USABLE
IDX_ARTISAN_ID P2 P1_2 USABLE
IDX_ARTISAN_ID P2 P2_2 USABLE
IDX_ARTISAN_ID P2 P3_2 USABLE
IDX_ARTISAN_ID P2 P4_2 USABLE
IDX_ARTISAN_ID P2 P5_2 USABLE
IDX_ARTISAN_ID P2 P6_2 USABLE
IDX_ARTISAN_ID P2 P7_2 USABLE
IDX_ARTISAN_ID P2 P8_2 USABLE--1.5 写入数据 OK insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)values (8888, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 1);insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)values (9999, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 2);commit;select * from GLOBAL_INDEX_PRIMARYKEY a where a.test_primarykey_id in (8888,9999);8888 345 2018-06-14 2018-06-14 2018-06-14 1
9999 345 2018-06-14 2018-06-14 2018-06-14 2Step4. 探究exchange分区对global索引以及local索引的影响
Step4.1 不指定update global indexes的场景
导致全局索引失效，分区索引失效，向表中写入数据失败，抛出 ORA-01502: index "ARTISAN.PK_GLOBAL_INDEX" or partition of such index is in unusable state ，需要重建全局索引和分区索引
select a.table_name ,a.index_name,a.status from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';GLOBAL_INDEX_PRIMARYKEY PK_GLOBAL_INDEX VALID
GLOBAL_INDEX_PRIMARYKEY IDX_ARTISAN_ID N/Aselect a.index_name, a.partition_name, a.subpartition_name, a.status from user_ind_subpartitions a where a.index_name in
(select a.index_name from user_ind_partitions a where a.index_name in
(select a.index_name from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY')); -- USABLE --- 1.1 1 40条数据 select count(1) from GLOBAL_INDEX_PRIMARYKEY subpartition(P1_1) a ; -- 8-- 1.2 创建临时表，并 exchange 分区 不加 update global indexes的情况 create table TEMP_ARTISAN as select * from GLOBAL_INDEX_PRIMARYKEY where 1=2 ;alter table GLOBAL_INDEX_PRIMARYKEY exchange subpartition P1_1 with table TEMP_ARTISAN ; -- 如果想交换 P1 这个包含子分区的分区，就不能使用 non-partitioned table Message:
ORA-14291: cannot EXCHANGE a composite partition with a non-partitioned table
Cause:
A composite partition can only be exchanged with a partitioned table.
Action:
Ensure that the table being exchanged is partitioned or that that the partition being exchanged is non-composite.-- 1.3 全局索引 ---------------- 失效 UNUSABLE状态select a.table_name ,a.index_name,a.status from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';GLOBAL_INDEX_PRIMARYKEY PK_GLOBAL_INDEX UNUSABLE
GLOBAL_INDEX_PRIMARYKEY IDX_ARTISAN_ID N/A--1.4 local索引 ------------- 失效select a.index_name, a.partition_name, a.subpartition_name, a.status from user_ind_subpartitions a where a.index_name in
(select a.index_name from user_ind_partitions a where a.index_name in
(select a.index_name from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY'));1 IDX_ARTISAN_ID P1 P1_1 UNUSABLE--1.5 写入数据 insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)values (8888, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 1);ORA-01502: index "CC.PK_GLOBAL_INDEX" or partition of such index is in unusable state
Cause: An attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation
Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition
-- 1.5 重建 全局索引 和 local 索引
alter index PK_GLOBAL_INDEX rebuild online nologging;insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)values (8888, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 1);insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)values (9999, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 2);--写入OK --重建local索引alter index IDX_ARTISAN_ID rebuild subpartition P1_1 online;-- 1.6 重新查询 select table_name,index_name,status from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';GLOBAL_INDEX_PRIMARYKEY PK_GLOBAL_INDEX VALID
GLOBAL_INDEX_PRIMARYKEY IDX_ARTISAN_ID N/Aselect a.index_name, a.partition_name, a.subpartition_name, a.status from user_ind_subpartitions a where a.index_name in
(select a.index_name from user_ind_partitions a where a.index_name in
(select a.index_name from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY')) and a.subpartition_name = 'P1_1';IDX_ARTISAN_ID P1 P1_1 USABLE -- OKStep4.2指定update global indexes的场景
因为指定了update global indexes ，全局索引VALID ，但是分区索引失效了，需要重建local索引。
--- 2.1 select a.table_name ,a.index_name,a.status from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';GLOBAL_INDEX_PRIMARYKEY PK_GLOBAL_INDEX VALID
GLOBAL_INDEX_PRIMARYKEY IDX_ARTISAN_ID N/Aselect a.index_name, a.partition_name, a.subpartition_name, a.status from user_ind_subpartitions a where a.index_name in
(select a.index_name from user_ind_partitions a where a.index_name in
(select a.index_name from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY')); -- USABLE --- 1.1 select count(1) from GLOBAL_INDEX_PRIMARYKEY subpartition(P1_2) a ; -- 12-- 1.2 创建临时表，并 exchange 分区 加 update global indexes的情况 create table TEMP_ARTISAN_2 as select * from GLOBAL_INDEX_PRIMARYKEY where 1=2 ;alter table GLOBAL_INDEX_PRIMARYKEY exchange subpartition P1_1 with table TEMP_ARTISAN_2 UPDATE GLOBAL INDEXES ; -- 1.3 全局索引 ---------------- VALIDselect a.table_name ,a.index_name,a.status from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';GLOBAL_INDEX_PRIMARYKEY PK_GLOBAL_INDEX VALID
GLOBAL_INDEX_PRIMARYKEY IDX_ARTISAN_ID N/A--1.4 local索引 ------------- USABLE OK select a.index_name, a.partition_name, a.subpartition_name, a.status from user_ind_subpartitions a where a.index_name in
(select a.index_name from user_ind_partitions a where a.index_name in
(select a.index_name from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY'));--1.5 写入数据 OK insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)values (8888, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 1);-- 1.6 重新查询 select table_name,index_name,status from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';GLOBAL_INDEX_PRIMARYKEY PK_GLOBAL_INDEX VALID
GLOBAL_INDEX_PRIMARYKEY IDX_ARTISAN_ID N/Aselect a.index_name, a.partition_name, a.subpartition_name, a.status from user_ind_subpartitions a where a.index_name in
(select a.index_name from user_ind_partitions a where a.index_name in
(select a.index_name from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY')) and a.subpartition_name = 'P1_2';IDX_ARTISAN_ID P2 P1_2 USABLE -- OKStep5 附加
释放回收空间：
alter table table_name truncate partition partition_name drop storage;
维护全局索引：
alter table table_name truncate partition partition_name update global indexes;
UPDATE GLOBAL INDEXES只维护全局索引
UPDATE INDEXES同时维护全局和本地索引， 经验证，local索引也会失效。 11.2.0.4.0 的版本 。 慎重使用。
INCLUDING INDEXES : 交换分区的同时，也将索引包含进去。 需要新建索引名
ALTER TABLE table_name EXCHANGE PARTITION partition_name WITH TABLE table_name_new INCLUDING INDEXES UPDATE GLOBAL INDEXES