Oracle大批量高效地更新大数据表索引字段

更新索引字段会触发索引重建,如果是要大批量修改大数据表的索引字段,速度会很慢,时间大多花在重建索引上了。
高效的办法是将索引先unusable,待批次更新修改后再重建(rebuild)索引。
整理語法如下,需要注意:

1. 通过索引判断一个table是否大数据表(大于50M)。
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
76
77
78
79
80
81
82
83
84
85
86
DECLARE
CURSOR c1 IS
SELECT index_name,PARTITIONED
FROM user_indexes
WHERE index_name IN (
SELECT DISTINCT INDEX_NAME
FROM user_ind_columns
WHERE TABLE_NAME = 'TABLE_NAME' --表名
AND column_name = 'COLUMN_NAME' --更新字段
AND TABLE_NAME IN (
SELECT segment_name
FROM (
SELECT segment_name
,SUM(siz_M) AS siz_M
FROM (
SELECT segment_name
,segment_type
,bytes / 1024 / 1024 AS siz_M
FROM user_segments
WHERE segment_type LIKE 'TABLE%'
AND segment_name = 'TABLE_NAME' --表名
)
GROUP BY segment_name
)
WHERE siz_M >= 50 --定义达到50M的表为大数据表
)
);
TYPE IDXINFOREC
IS TABLE OF c1%ROWTYPE INDEX BY BINARY_INTEGER;
idxinfo c1%ROWTYPE;
idxinfolist IDXINFOREC;
counter INTEGER;
BEGIN
counter := 0;
OPEN c1;
LOOP
FETCH c1 INTO idxinfo;
IF c1%FOUND THEN
counter := counter + 1;
END IF;
idxinfolist(counter) := idxinfo;
IF c1%NOTFOUND THEN
EXIT;
END IF;
END LOOP;
CLOSE c1;
--1.unusable索引
FOR i IN 1..counter LOOP
EXECUTE immediate 'ALTER INDEX ' || idxinfolist(i).index_name || ' unusable';
END LOOP;
--2.中间执行更新任务
-- update something
COMMIT;
--3.重建索引
FOR i IN 1..counter LOOP
IF idxinfolist(i).PARTITIONED = 'NO' THEN
EXECUTE immediate 'ALTER INDEX ' || idxinfolist(i).index_name || ' rebuild parallel nologging';
ELSE
IF idxinfolist(i).PARTITIONED = 'YES' THEN
FOR x IN (
SELECT PARTITION_NAME
FROM user_IND_PARTITIONS
WHERE index_name = idxinfolist(i).index_name
ORDER BY PARTITION_POSITION
) LOOP
EXECUTE immediate 'ALTER INDEX ' || idxinfolist(i).index_name || ' rebuild PARTITION ' || x.PARTITION_NAME || ' parallel nologging';
END LOOP;
END IF;
END IF;
EXECUTE immediate 'ALTER INDEX ' || idxinfolist(i).index_name || ' noparallel';
END LOOP;
END;