概述

ORACLE关于维护分区表的官方指导文档：

我们知道，当将表中某个字段设置为主键的时候，oracle会自动的创建一个同名的唯一性索引。分区表亦是如此。

案例

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 ;--11

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 VALIDGLOBAL_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                       USABLEIDX_ARTISAN_ID P1                           P2_1                       USABLEIDX_ARTISAN_ID P1                           P3_1                       USABLEIDX_ARTISAN_ID P1                           P4_1                       USABLEIDX_ARTISAN_ID P1                           P5_1                       USABLEIDX_ARTISAN_ID P1                           P6_1                       USABLEIDX_ARTISAN_ID P1                           P7_1                       USABLEIDX_ARTISAN_ID P1                           P8_1                       USABLEIDX_ARTISAN_ID P2                           P1_2                       USABLEIDX_ARTISAN_ID P2                           P2_2                       USABLEIDX_ARTISAN_ID P2                           P3_2                       USABLEIDX_ARTISAN_ID P2                           P4_2                       USABLEIDX_ARTISAN_ID P2                           P5_2                       USABLEIDX_ARTISAN_ID P2                           P6_2                       USABLEIDX_ARTISAN_ID P2                           P7_2                       USABLEIDX_ARTISAN_ID P2                           P8_2                       USABLE16 rows selectedSQL>

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 UNUSABLEGLOBAL_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    USABLEIDX_ARTISAN_ID  P1  P2_1    USABLEIDX_ARTISAN_ID  P1  P3_1    USABLEIDX_ARTISAN_ID  P1  P4_1    USABLEIDX_ARTISAN_ID  P1  P5_1    USABLEIDX_ARTISAN_ID  P1  P6_1    USABLEIDX_ARTISAN_ID  P1  P7_1    USABLEIDX_ARTISAN_ID  P1  P8_1    USABLEIDX_ARTISAN_ID  P2  P1_2    USABLEIDX_ARTISAN_ID  P2  P2_2    USABLEIDX_ARTISAN_ID  P2  P3_2    USABLEIDX_ARTISAN_ID  P2  P4_2    USABLEIDX_ARTISAN_ID  P2  P5_2    USABLEIDX_ARTISAN_ID  P2  P6_2    USABLEIDX_ARTISAN_ID  P2  P7_2    USABLEIDX_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 VALIDGLOBAL_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 VALIDGLOBAL_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 VALIDGLOBAL_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    USABLEIDX_ARTISAN_ID  P1  P2_1    USABLEIDX_ARTISAN_ID  P1  P3_1    USABLEIDX_ARTISAN_ID  P1  P4_1    USABLEIDX_ARTISAN_ID  P1  P5_1    USABLEIDX_ARTISAN_ID  P1  P6_1    USABLEIDX_ARTISAN_ID  P1  P7_1    USABLEIDX_ARTISAN_ID  P1  P8_1    USABLEIDX_ARTISAN_ID  P2  P1_2    USABLEIDX_ARTISAN_ID  P2  P2_2    USABLEIDX_ARTISAN_ID  P2  P3_2    USABLEIDX_ARTISAN_ID  P2  P4_2    USABLEIDX_ARTISAN_ID  P2  P5_2    USABLEIDX_ARTISAN_ID  P2  P6_2    USABLEIDX_ARTISAN_ID  P2  P7_2    USABLEIDX_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  19999    345 2018-06-14  2018-06-14  2018-06-14  2

Step4. 探究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 VALIDGLOBAL_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 tableCause: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 UNUSABLEGLOBAL_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 VALIDGLOBAL_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   -- OK

Step4.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 VALIDGLOBAL_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 VALIDGLOBAL_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 VALIDGLOBAL_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  -- OK

Step5 附加

释放回收空间：

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

你可能感兴趣的文章