概述
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