Oracle-维护存在主键的分区表时的注意事项

举报
小工匠 发表于 2021/09/11 00:51:29 2021/09/11
【摘要】 文章目录 概述案例Step1.新建测试表，构造测试数据Step2. 查看索引状态Step2.1 普通索引Step2.2 分区索引Step2.3 子分区索引 Step3. 探究trunca...

文章目录

在这里插入图片描述

概述

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

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

案例

Step1.新建测试表，构造测试数据

我们这里建立一个list-hash的复合分区的测试表 ,同时为ARTISAN_ID这个字段创建了local索引,同时将test_primarykey_id 设置为主键。

-- Create table
create 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
  );

  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    • 
  
  • 5
    • 
  
  • 6
    • 
  
  • 7
    • 
  
  • 8
    • 
  
  • 9
    • 
  
  • 10
    • 
  
  • 11
    • 
  
  • 12
    • 
  
  • 13
    • 
  
  • 14
    • 
  
  • 15
    • 
  
  • 16
    • 
  
  • 17
    • 
  
  • 18
    • 
  
  • 19
    • 
  
  • 20
    • 
  
  • 21
    • 
  
  • 22
    • 
  
  • 23
    • 
  
  • 24
    • 
  
  • 25
    • 
  
  • 26
    • 
  
  • 27
    • 
  
  • 28
    • 
  
  • 29
    • 
  
  • 30
    • 
  
  • 31
    • 
  
  • 32
    • 
  
  • 33
    • 
  
  • 34
    • 
  
  • 35
    • 
  
  • 36
    • 
  
  • 37
    • 
  
  • 38
    • 
  
  • 39
    • 
  
  • 40
    • 
  
  • 41
    • 
  
  • 42
    • 
  
  • 43
    • 
  
  • 44
    • 
  
  • 45
    • 
  
  • 46
    • 
  
  • 47
    • 
  
  • 48
    • 
  
  • 49
    • 
  
  • 50
    • 
  
  • 51
    • 
  
  • 52
    • 
  
  • 53
    • 
  
  • 54
    • 
  
  • 55
    • 
  
  • 56
    • 
  
  • 57
    • 
  
  • 58
    • 
  
  • 59
    • 
  
  • 60
    • 
  
  • 61
    • 
  
  • 62
    •

PLSQL中查看

这里写图片描述

这里写图片描述

然后构造部分数据

select count(1)  from GLOBAL_INDEX_PRIMARYKEY  partition(p1) a ;  -- 140
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p1_1) a ; --8
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p2_1) a ; -- 19
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p3_1) a ;-- 21
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p4_1) a ;-- 13
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p5_1) a ;-- 16
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p6_1) a ;-- 25
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p7_1) a ;-- 16
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p8_1) a ;-- 22


select count(1)  from GLOBAL_INDEX_PRIMARYKEY  partition(p2) a ; -- 90
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p1_2) a ;--12
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p2_2) a ;--13
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p3_2) a ;--9
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p4_2) a ;--6
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p5_2) a ;--13
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p6_2) a ;--10
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p7_2) a ;--16
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p8_2) a ;--11

  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    • 
  
  • 5
    • 
  
  • 6
    • 
  
  • 7
    • 
  
  • 8
    • 
  
  • 9
    • 
  
  • 10
    • 
  
  • 11
    • 
  
  • 12
    • 
  
  • 13
    • 
  
  • 14
    • 
  
  • 15
    • 
  
  • 16
    • 
  
  • 17
    • 
  
  • 18
    • 
  
  • 19
    • 
  
  • 20
    •

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/A
 
SQL> 

  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    • 
  
  • 5
    • 
  
  • 6
    • 
  
  • 7
    • 
  
  • 8
    •

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/A
P2                           IDX_ARTISAN_ID N/A
 
SQL> 

  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    • 
  
  • 5
    • 
  
  • 6
    • 
  
  • 7
    • 
  
  • 8
    • 
  
  • 9
    • 
  
  • 10
    • 
  
  • 11
    • 
  
  • 12
    •

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                       USABLE
 
16 rows selected
 
SQL> 

  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    • 
  
  • 5
    • 
  
  • 6
    • 
  
  • 7
    • 
  
  • 8
    • 
  
  • 9
    • 
  
  • 10
    • 
  
  • 11
    • 
  
  • 12
    • 
  
  • 13
    • 
  
  • 14
    • 
  
  • 15
    • 
  
  • 16
    • 
  
  • 17
    • 
  
  • 18
    • 
  
  • 19
    • 
  
  • 20
    • 
  
  • 21
    • 
  
  • 22
    • 
  
  • 23
    • 
  
  • 24
    • 
  
  • 25
    • 
  
  • 26
    • 
  
  • 27
    • 
  
  • 28
    • 
  
  • 29
    • 
  
  • 30
    • 
  
  • 31
    • 
  
  • 32
    •

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状态，OK
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/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的.  回滚吧,暂时不写入表里.




  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    • 
  
  • 5
    • 
  
  • 6
    • 
  
  • 7
    • 
  
  • 8
    • 
  
  • 9
    • 
  
  • 10
    • 
  
  • 11
    • 
  
  • 12
    • 
  
  • 13
    • 
  
  • 14
    • 
  
  • 15
    • 
  
  • 16
    • 
  
  • 17
    • 
  
  • 18
    • 
  
  • 19
    • 
  
  • 20
    • 
  
  • 21
    • 
  
  • 22
    • 
  
  • 23
    • 
  
  • 24
    • 
  
  • 25
    • 
  
  • 26
    • 
  
  • 27
    • 
  
  • 28
    • 
  
  • 29
    • 
  
  • 30
    • 
  
  • 31
    • 
  
  • 32
    • 
  
  • 33
    • 
  
  • 34
    • 
  
  • 35
    • 
  
  • 36
    • 
  
  • 37
    • 
  
  • 38
    • 
  
  • 39
    • 
  
  • 40
    • 
  
  • 41
    • 
  
  • 42
    • 
  
  • 43
    • 
  
  • 44
    • 
  
  • 45
    • 
  
  • 46
    • 
  
  • 47
    • 
  
  • 48
    • 
  
  • 49
    • 
  
  • 50
    • 
  
  • 51
    • 
  
  • 52
    • 
  
  • 53
    • 
  
  • 54
    • 
  
  • 55
    • 
  
  • 56
    • 
  
  • 57
    • 
  
  • 58
    • 
  
  • 59
    • 
  
  • 60
    • 
  
  • 61
    • 
  
  • 62
    • 
  
  • 63
    • 
  
  • 64
    • 
  
  • 65
    • 
  
  • 66
    • 
  
  • 67
    • 
  
  • 68
    • 
  
  • 69
    • 
  
  • 70
    • 
  
  • 71
    • 
  
  • 72
    • 
  
  • 73
    • 
  
  • 74
    • 
  
  • 75
    • 
  
  • 76
    • 
  
  • 77
    • 
  
  • 78
    • 
  
  • 79
    • 
  
  • 80
    • 
  
  • 81
    • 
  
  • 82
    • 
  
  • 83
    • 
  
  • 84
    • 
  
  • 85
    • 
  
  • 86
    • 
  
  • 87
    • 
  
  • 88
    • 
  
  • 89
    • 
  
  • 90
    •

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    全局索引  ---------------- 有效   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.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	2



  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    • 
  
  • 5
    • 
  
  • 6
    • 
  
  • 7
    • 
  
  • 8
    • 
  
  • 9
    • 
  
  • 10
    • 
  
  • 11
    • 
  
  • 12
    • 
  
  • 13
    • 
  
  • 14
    • 
  
  • 15
    • 
  
  • 16
    • 
  
  • 17
    • 
  
  • 18
    • 
  
  • 19
    • 
  
  • 20
    • 
  
  • 21
    • 
  
  • 22
    • 
  
  • 23
    • 
  
  • 24
    • 
  
  • 25
    • 
  
  • 26
    • 
  
  • 27
    • 
  
  • 28
    • 
  
  • 29
    • 
  
  • 30
    • 
  
  • 31
    • 
  
  • 32
    • 
  
  • 33
    • 
  
  • 34
    • 
  
  • 35
    • 
  
  • 36
    • 
  
  • 37
    • 
  
  • 38
    • 
  
  • 39
    • 
  
  • 40
    • 
  
  • 41
    • 
  
  • 42
    • 
  
  • 43
    • 
  
  • 44
    • 
  
  • 45
    • 
  
  • 46
    • 
  
  • 47
    • 
  
  • 48
    • 
  
  • 49
    • 
  
  • 50
    • 
  
  • 51
    • 
  
  • 52
    • 
  
  • 53
    • 
  
  • 54
    • 
  
  • 55
    • 
  
  • 56
    • 
  
  • 57
    • 
  
  • 58
    • 
  
  • 59
    • 
  
  • 60
    • 
  
  • 61
    • 
  
  • 62
    • 
  
  • 63
    • 
  
  • 64
    • 
  
  • 65
    • 
  
  • 66
    • 
  
  • 67
    • 
  
  • 68
    • 
  
  • 69
    • 
  
  • 70
    • 
  
  • 71
    • 
  
  • 72
    • 
  
  • 73
    • 
  
  • 74
    • 
  
  • 75
    •

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	VALID
GLOBAL_INDEX_PRIMARYKEY	IDX_ARTISAN_ID	N/A


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'));  -- 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/A


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'))
                 and a.subpartition_name = 'P1_1';

IDX_ARTISAN_ID	P1	P1_1	USABLE   -- OK  




  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    • 
  
  • 5
    • 
  
  • 6
    • 
  
  • 7
    • 
  
  • 8
    • 
  
  • 9
    • 
  
  • 10
    • 
  
  • 11
    • 
  
  • 12
    • 
  
  • 13
    • 
  
  • 14
    • 
  
  • 15
    • 
  
  • 16
    • 
  
  • 17
    • 
  
  • 18
    • 
  
  • 19
    • 
  
  • 20
    • 
  
  • 21
    • 
  
  • 22
    • 
  
  • 23
    • 
  
  • 24
    • 
  
  • 25
    • 
  
  • 26
    • 
  
  • 27
    • 
  
  • 28
    • 
  
  • 29
    • 
  
  • 30
    • 
  
  • 31
    • 
  
  • 32
    • 
  
  • 33
    • 
  
  • 34
    • 
  
  • 35
    • 
  
  • 36
    • 
  
  • 37
    • 
  
  • 38
    • 
  
  • 39
    • 
  
  • 40
    • 
  
  • 41
    • 
  
  • 42
    • 
  
  • 43
    • 
  
  • 44
    • 
  
  • 45
    • 
  
  • 46
    • 
  
  • 47
    • 
  
  • 48
    • 
  
  • 49
    • 
  
  • 50
    • 
  
  • 51
    • 
  
  • 52
    • 
  
  • 53
    • 
  
  • 54
    • 
  
  • 55
    • 
  
  • 56
    • 
  
  • 57
    • 
  
  • 58
    • 
  
  • 59
    • 
  
  • 60
    • 
  
  • 61
    • 
  
  • 62
    • 
  
  • 63
    • 
  
  • 64
    • 
  
  • 65
    • 
  
  • 66
    • 
  
  • 67
    • 
  
  • 68
    • 
  
  • 69
    • 
  
  • 70
    • 
  
  • 71
    • 
  
  • 72
    • 
  
  • 73
    • 
  
  • 74
    • 
  
  • 75
    • 
  
  • 76
    • 
  
  • 77
    • 
  
  • 78
    • 
  
  • 79
    • 
  
  • 80
    • 
  
  • 81
    • 
  
  • 82
    • 
  
  • 83
    • 
  
  • 84
    • 
  
  • 85
    • 
  
  • 86
    • 
  
  • 87
    • 
  
  • 88
    • 
  
  • 89
    • 
  
  • 90
    • 
  
  • 91
    • 
  
  • 92
    • 
  
  • 93
    • 
  
  • 94
    • 
  
  • 95
    • 
  
  • 96
    • 
  
  • 97
    • 
  
  • 98
    • 
  
  • 99
    • 
  
  • 100
    • 
  
  • 101
    • 
  
  • 102
    • 
  
  • 103
    • 
  
  • 104
    • 
  
  • 105
    • 
  
  • 106
    • 
  
  • 107
    • 
  
  • 108
    • 
  
  • 109
    •

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	VALID
GLOBAL_INDEX_PRIMARYKEY	IDX_ARTISAN_ID	N/A


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'));  -- 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    全局索引  ----------------  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


--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/A


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'))
                 and a.subpartition_name = 'P1_2';

IDX_ARTISAN_ID	P2	P1_2	USABLE
  -- OK  

  
 
    
  
  • 1
    • 
  
  • 2
    • 
  
  • 3
    • 
  
  • 4
    • 
  
  • 5
    • 
  
  • 6
    • 
  
  • 7
    • 
  
  • 8
    • 
  
  • 9
    • 
  
  • 10
    • 
  
  • 11
    • 
  
  • 12
    • 
  
  • 13
    • 
  
  • 14
    • 
  
  • 15
    • 
  
  • 16
    • 
  
  • 17
    • 
  
  • 18
    • 
  
  • 19
    • 
  
  • 20
    • 
  
  • 21
    • 
  
  • 22
    • 
  
  • 23
    • 
  
  • 24
    • 
  
  • 25
    • 
  
  • 26
    • 
  
  • 27
    • 
  
  • 28
    • 
  
  • 29
    • 
  
  • 30
    • 
  
  • 31
    • 
  
  • 32
    • 
  
  • 33
    • 
  
  • 34
    • 
  
  • 35
    • 
  
  • 36
    • 
  
  • 37
    • 
  
  • 38
    • 
  
  • 39
    • 
  
  • 40
    • 
  
  • 41
    • 
  
  • 42
    • 
  
  • 43
    • 
  
  • 44
    • 
  
  • 45
    • 
  
  • 46
    • 
  
  • 47
    • 
  
  • 48
    • 
  
  • 49
    • 
  
  • 50
    • 
  
  • 51
    • 
  
  • 52
    • 
  
  • 53
    • 
  
  • 54
    • 
  
  • 55
    • 
  
  • 56
    • 
  
  • 57
    • 
  
  • 58
    • 
  
  • 59
    • 
  
  • 60
    • 
  
  • 61
    • 
  
  • 62
    • 
  
  • 63
    • 
  
  • 64
    • 
  
  • 65
    • 
  
  • 66
    • 
  
  • 67
    • 
  
  • 68
    • 
  
  • 69
    • 
  
  • 70
    • 
  
  • 71
    • 
  
  • 72
    • 
  
  • 73
    • 
  
  • 74
    • 
  
  • 75
    • 
  
  • 76
    •

Step5 附加

释放回收空间：

alter table table_name truncate partition partition_name drop storage;

  
 
    
  
  • 1
    •

维护全局索引：

alter table table_name truncate  partition partition_name update global indexes;

  
 
    
  
  • 1
    •

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

  
 
    
  
  • 1
    •

文章来源: artisan.blog.csdn.net，作者：小小工匠，版权归原作者所有，如需转载，请联系作者。

原文链接：artisan.blog.csdn.net/article/details/80694081

【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
Oracle
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入