Oracle 分区和分区索引
Oracle 分区和分区索引
前言
记录一下 Oracle 的分区表和索引官方文档位置，以及分区索引的失效操作记录，方便后续查询
分区表和分区索引
About Merging Partitions and Subpartitions
测试样例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 创建分区表（按范围分区）
CREATE TABLE part_tab (
id NUMBER,
col3 NUMBER,
create_date DATE
)
PARTITION BY RANGE (col3) (
PARTITION p1 VALUES LESS THAN (10000),
PARTITION p2 VALUES LESS THAN (20000),
PARTITION p_max VALUES LESS THAN (MAXVALUE)
);
-- 插入测试数据
-- 落入 p1
INSERT INTO part_tab VALUES (1, 5000, SYSDATE);
-- 落入 p2
INSERT INTO part_tab VALUES (2, 15000, SYSDATE);
-- 落入 p_max
INSERT INTO part_tab VALUES (3, 25000, SYSDATE);
COMMIT;
-- 创建全局索引和局部索引
CREATE /* 全局索引 */ INDEX gidx_part_id ON part_tab(id) GLOBAL;
CREATE /* 局部索引 */INDEX lidx_part_col3 ON part_tab(col3) LOCAL;
测试场景
Truncate
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
-- 执行 Truncate 分区（不更新索引）
ALTER TABLE part_tab TRUNCATE PARTITION p1;
-- 验证全局索引状态
SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
WXJ@pdb1 19,1109022 SQL> SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
INDEX_NAME
----------------------------------------------------------------------------------------------------
STATUS
--------
GIDX_PART_ID
UNUSABLE
Elapsed: 00:00:00.05
WXJ@pdb1 19,1109022 SQL> set linesize 300
WXJ@pdb1 19,1109022 SQL>
WXJ@pdb1 19,1109022 SQL> /
INDEX_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
GIDX_PART_ID UNUSABLE
-- 验证局部索引状态
SELECT index_name, partition_name, status
FROM user_ind_partitions
WHERE index_name = 'LIDX_PART_COL3'
AND partition_name = 'P1';
WXJ@pdb1 19,1109022 SQL> SELECT index_name, partition_name, status
2 FROM user_ind_partitions
3 WHERE index_name = 'LIDX_PART_COL3'
4 AND partition_name = 'P1';
INDEX_NAME PARTITION_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------
LIDX_PART_COL3 P1 USABLE
-- 手动重建/更新全局索引
alter index WXJ.GIDX_PART_ID rebuild ;
WXJ@pdb1 19,1109022 SQL> alter index WXJ.GIDX_PART_ID rebuild ;
Index altered.
Elapsed: 00:00:00.04
WXJ@pdb1 19,1109022 SQL>
WXJ@pdb1 19,1109022 SQL> SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
INDEX_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
GIDX_PART_ID VALID
可以看出，Truncate 分区后，全局索引失效，局部索引不受影响。如果避免全局索引失效，可以在 Truncate 分区时指定UPDATE GLOBAL INDEXES
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 避免失效操作（重新执行并更新索引）
ALTER TABLE part_tab TRUNCATE PARTITION p1 UPDATE GLOBAL INDEXES;
-- 再次验证全局索引状态（预期：VALID）
WXJ@pdb1 19,1109022 SQL> ALTER TABLE part_tab TRUNCATE PARTITION p1 UPDATE GLOBAL INDEXES;
Table truncated.
Elapsed: 00:00:00.01
WXJ@pdb1 19,1109022 SQL> SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
INDEX_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
GIDX_PART_ID VALID
Elapsed: 00:00:00.01
Drop 分区
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
110
111
112
113
114
115
--查询分区表详情
col OWNER for a10
col SEGMENT_NAME for a25
col PARTITION_NAME for a30
set lin 200 pagesize 500
select OWNER,SEGMENT_NAME,PARTITION_NAME from dba_segments where OWNER in ('WXJ') and SEGMENT_NAME in ('PART_TAB') order by 2,3;
WXJ@pdb1 19,1109022 SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME from dba_segments where OWNER in ('WXJ') and SEGMENT_NAME in ('PART_TAB') order by 2,3;
OWNER SEGMENT_NAME PARTITION_NAME
---------- ------------------------- ------------------------------
WXJ PART_TAB P1
WXJ PART_TAB P2
WXJ PART_TAB P_MAX
-- 执行 Drop 分区（不更新索引）
ALTER TABLE part_tab DROP PARTITION p2;
WXJ@pdb1 19,1109022 SQL> ALTER TABLE part_tab DROP PARTITION p2;
Table altered.
Elapsed: 00:00:00.02
WXJ@pdb1 19,1109022 SQL>
WXJ@pdb1 19,1109022 SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME from dba_segments where OWNER in ('WXJ') and SEGMENT_NAME in ('PART_TAB') order by 2,3;
OWNER SEGMENT_NAME PARTITION_NAME
---------- ------------------------- ------------------------------
WXJ PART_TAB P1
WXJ PART_TAB P_MAX
-- 验证全局索引状态（预期：UNUSABLE）
SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
WXJ@pdb1 19,1109022 SQL> SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
INDEX_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
GIDX_PART_ID UNUSABLE
-- 查看全局索引情况
WXJ@pdb1 19,1109022 SQL> SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
WXJ@pdb1 19,1109022 SQL> SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
INDEX_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
GIDX_PART_ID UNUSABLE
Elapsed: 00:00:00.00
WXJ@pdb1 19,1109022 SQL> -- 验证局部索引状态
WXJ@pdb1 19,1109022 SQL> SELECT index_name, partition_name, status FROM user_ind_partitions WHERE index_name = 'LIDX_PART_COL3' ;
INDEX_NAME PARTITION_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------ --------
LIDX_PART_COL3 P1 USABLE
LIDX_PART_COL3 P_MAX USABLE
-- 重新添加分区
-- ALTER TABLE part_tab ADD PARTITION p2 VALUES LESS THAN (20000);
ALTER TABLE part_tab
SPLIT PARTITION p_max AT (30000)
INTO (PARTITION p2, PARTITION p_max);
WXJ@pdb1 19,1109022 SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME from dba_segments where OWNER in ('WXJ') and SEGMENT_NAME in ('PART_TAB') order by 2,3;
OWNER SEGMENT_NAME PARTITION_NAME
---------- ------------------------- ------------------------------
WXJ PART_TAB P1
WXJ PART_TAB P2
WXJ PART_TAB P_MAX
-- 再次检查索引状态
WXJ@pdb1 19,1109022 SQL> SELECT index_name, partition_name, status FROM user_ind_partitions WHERE index_name = 'LIDX_PART_COL3' ;
INDEX_NAME PARTITION_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------ --------
LIDX_PART_COL3 P1 USABLE
LIDX_PART_COL3 P2 USABLE -- 重建后的本地索引状态正常
LIDX_PART_COL3 P_MAX USABLE
WXJ@pdb1 19,1109022 SQL> SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
INDEX_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
GIDX_PART_ID UNUSABLE
-- 重建全局索引
WXJ@pdb1 19,1109022 SQL> alter index WXJ.GIDX_PART_ID rebuild ;
Index altered.
Elapsed: 00:00:00.01
WXJ@pdb1 19,1109022 SQL>
WXJ@pdb1 19,1109022 SQL> SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
INDEX_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
GIDX_PART_ID VALID
-- 避免失效操作（带 UPDATE GLOBAL INDEXES）
ALTER TABLE part_tab DROP PARTITION p2 UPDATE GLOBAL INDEXES;
WXJ@pdb1 19,1109022 SQL> ALTER TABLE part_tab DROP PARTITION p2 UPDATE GLOBAL INDEXES;
Table altered.
Elapsed: 00:00:00.02
WXJ@pdb1 19,1109022 SQL>
WXJ@pdb1 19,1109022 SQL> SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
INDEX_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
GIDX_PART_ID VALID
Elapsed: 00:00:00.00
-- 验证全局索引状态（预期：VALID）
Split 分区（有记录的 MAX 分区）
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
110
111
112
113
114
115
-- 确保 p_max 有数据
SELECT * FROM part_tab PARTITION (p_max);
WXJ@pdb1 19,1109022 SQL> SELECT * FROM part_tab PARTITION (p_max);
no rows selected
Elapsed: 00:00:00.00
-- 上面 split p2 的时候搞错了范围，重新插入新的数据
INSERT INTO part_tab VALUES (4, 30000, SYSDATE);
INSERT INTO part_tab VALUES (5, 50000, SYSDATE);
INSERT INTO part_tab VALUES (6, 60000, SYSDATE);
INSERT INTO part_tab VALUES (7, 70000, SYSDATE);
COMMIT;
-- 执行 Split 分区
ALTER TABLE part_tab
SPLIT PARTITION p_max AT (40000)
INTO (PARTITION p3, PARTITION p_max);
WXJ@pdb1 19,1109022 SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME from dba_segments where OWNER in ('WXJ') and SEGMENT_NAME in ('PART_TAB') order by 2,3;
OWNER SEGMENT_NAME PARTITION_NAME
---------- ------------------------- ------------------------------
WXJ PART_TAB P1
WXJ PART_TAB P3
WXJ PART_TAB P_MAX
-- 验证全局索引状态（预期：UNUSABLE）
SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
WXJ@pdb1 19,1109022 SQL> SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
INDEX_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
GIDX_PART_ID UNUSABLE
-- 验证局部索引状态（预期：UNUSABLE）
SELECT index_name, partition_name, status
FROM user_ind_partitions
WHERE index_name = 'LIDX_PART_COL3'
AND partition_name IN ('P3', 'P_MAX');
INDEX_NAME PARTITION_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------ --------
LIDX_PART_COL3 P3 UNUSABLE
LIDX_PART_COL3 P_MAX UNUSABLE
-- 重建全局索引
WXJ@pdb1 19,1109022 SQL> alter index WXJ.GIDX_PART_ID rebuild ;
Index altered.
-- 重建局部索引
ALTER INDEX lidx_part_col3 REBUILD PARTITION p3;
ALTER INDEX lidx_part_col3 REBUILD PARTITION p_max;
WXJ@pdb1 19,1109022 SQL> ALTER INDEX lidx_part_col3 REBUILD PARTITION p3;
Index altered.
Elapsed: 00:00:00.02
WXJ@pdb1 19,1109022 SQL> ALTER INDEX lidx_part_col3 REBUILD PARTITION p_max;
Index altered.
Elapsed: 00:00:00.02
-- 查看索引状态
SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
SELECT index_name, partition_name, status
FROM user_ind_partitions
WHERE index_name = 'LIDX_PART_COL3' ;
WXJ@pdb1 19,1109022 SQL> SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
INDEX_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
GIDX_PART_ID VALID
Elapsed: 00:00:00.00
WXJ@pdb1 19,1109022 SQL> SELECT index_name, partition_name, status FROM user_ind_partitions WHERE index_name = 'LIDX_PART_COL3' ;
INDEX_NAME PARTITION_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------ --------
LIDX_PART_COL3 P1 USABLE
LIDX_PART_COL3 P3 USABLE
LIDX_PART_COL3 P_MAX USABLE
Elapsed: 00:00:00.04
-- 使用 UPDATE GLOBAL INDEXES 避免失效
ALTER TABLE part_tab
SPLIT PARTITION p_max AT (50000)
INTO (PARTITION p4, PARTITION p_max)
UPDATE GLOBAL INDEXES;
-- 验证全局索引状态（预期：VALID）
WXJ@pdb1 19,1109022 SQL> SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
INDEX_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
GIDX_PART_ID VALID
Elapsed: 00:00:00.00
WXJ@pdb1 19,1109022 SQL> SELECT index_name, partition_name, status FROM user_ind_partitions WHERE index_name = 'LIDX_PART_COL3' ;
INDEX_NAME PARTITION_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------ --------
LIDX_PART_COL3 P1 USABLE
LIDX_PART_COL3 P3 USABLE
LIDX_PART_COL3 P4 USABLE
LIDX_PART_COL3 P_MAX USABLE
Elapsed: 00:00:00.00
Add 分区
已经存在 max 分区，无法直接通过 add partition 来添加分区，需要通过 split 分区来添加分区。可以新建一张表，然后 add partition 来测试。为了方便直接将 max 分区删除，然后重新添加分区
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
-- 删除 MAX 分区
ALTER TABLE part_tab DROP PARTITION p_max;
-- drop 分区会使全局索引失效，需要重建
alter index WXJ.GIDX_PART_ID rebuild ;
-- 验证状态
WXJ@pdb1 19,1109022 SQL> SELECT index_name, partition_name, status
2 FROM user_ind_partitions
3 WHERE index_name = 'LIDX_PART_COL3'
4 ;
INDEX_NAME PARTITION_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------ --------
LIDX_PART_COL3 P1 USABLE
LIDX_PART_COL3 P3 USABLE
LIDX_PART_COL3 P4 USABLE
Elapsed: 00:00:00.04
WXJ@pdb1 19,1109022 SQL> SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
INDEX_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
GIDX_PART_ID VALID
Elapsed: 00:00:00.01
-- 执行 Add 分区
ALTER TABLE part_tab
ADD PARTITION p6 VALUES LESS THAN (60000);
-- 验证全局索引和局部索引状态（预期：VALID）
SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
SELECT index_name, partition_name, status
FROM user_ind_partitions
WHERE index_name = 'LIDX_PART_COL3' ;
WXJ@pdb1 19,1109022 SQL> SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
INDEX_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
GIDX_PART_ID VALID
Elapsed: 00:00:00.00
WXJ@pdb1 19,1109022 SQL> SELECT index_name, partition_name, status
2 FROM user_ind_partitions
3 WHERE index_name = 'LIDX_PART_COL3' ;
INDEX_NAME PARTITION_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------ --------
LIDX_PART_COL3 P1 USABLE
LIDX_PART_COL3 P3 USABLE
LIDX_PART_COL3 P4 USABLE
LIDX_PART_COL3 P6 USABLE
-- 可以看到，Add 分区后，全局索引和局部索引状态都是正常的
Exchange 分区
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
-- 创建普通表并插入数据
CREATE TABLE normal_tab (id NUMBER, col3 NUMBER, create_date DATE);
INSERT INTO normal_tab VALUES (4, 7000, SYSDATE);
COMMIT;
create index idx_normal_col3 on normal_tab(col3);
-- 执行 Exchange 分区（不更新索引）
ALTER TABLE part_tab
EXCHANGE PARTITION p1
WITH TABLE normal_tab INCLUDING INDEXES;
-- 验证全局索引状态（预期：UNUSABLE）
SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
WXJ@pdb1 19,1109022 SQL> SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
INDEX_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
GIDX_PART_ID UNUSABLE
-- 避免失效操作（带 UPDATE GLOBAL INDEXES）
WXJ@pdb1 19,1109022 SQL> alter index WXJ.GIDX_PART_ID rebuild ;
ALTER TABLE part_tab
EXCHANGE PARTITION p1
WITH TABLE normal_tab INCLUDING INDEXES
UPDATE GLOBAL INDEXES;
-- 验证全局索引状态（预期：VALID）
WXJ@pdb1 19,1109022 SQL> SELECT index_name, status FROM user_indexes WHERE index_name = 'GIDX_PART_ID';
INDEX_NAME STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
GIDX_PART_ID VALID
Elapsed: 00:00:00.00
总结
不同的版本操作方法和影响会存在差异，最好查询官网文档
如果涉及的分区或子分区包含数据，则索引可能会被标记为 UNUSABLE，如下表所述：
| Table Type | Index Behavior |
|---|---|
| Regular (Heap) | Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:- The database marks UNUSABLE all resulting corresponding local index partitions or subpartitions.- Global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt. |
| Index-organized | The database marks UNUSABLE all resulting corresponding local index partitions.All global indexes remain usable. |
会造成分区表索引失效的操作
| 操作动作 | 操作命令 | 全局索引 | 分区索引 |
|---|---|---|---|
| Truncate | ALTER TABLE part_tab_trunc TRUNCATE PARTITION p1; |
是否失效: 失效 如何避免: UPDATE GLOBAL INDEXES; |
是否失效: 无影响 如何避免: 无需操作 |
| Drop | ALTER TABLE part_tab_drop DROP PARTITION p1; |
是否失效: 失效 如何避免: UPDATE GLOBAL INDEXES; |
是否失效: 无影响 如何避免: 无需操作 |
| Split | ALTER TABLE part_tab_split SPLIT PARTITION p_max AT (30000) INTO (PARTITION p3, PARTITION p_max); |
是否失效: 失效 如何避免: UPDATE GLOBAL INDEXES; |
是否失效: 失效（若 MAX 分区有记录） 如何避免: 重建局部索引 ALTER INDEX idx_part_split_col3 REBUILD; |
| Add | ALTER TABLE part_tab_add ADD PARTITION p6 VALUES LESS THAN (60000); |
是否失效: 无影响 如何避免: 无需操作 |
是否失效: 无影响 如何避免: 无需操作 |
| Exchange | ALTER TABLE part_tab_exch EXCHANGE PARTITION p1 WITH TABLE normal_tab INCLUDING INDEXES; |
是否失效: 失效 如何避免: UPDATE GLOBAL INDEXES; |
是否失效: 无影响 如何避免: 无需操作 |
本文由作者按照
CC BY 4.0
进行授权