10g中引入了对索引的shrink功能，索引shrink操作会扫描索引的页块，并且通过归并当前存在的数据将先前已删除记录的空间重新利用；很多书籍亦或者MOS的Note中都会提及SHRINK命令与早期版本中就存在的COALESCE(合并)命令具有完全相同的功能，或者说2者是完全等价的-" alter index shrink space is equivalent to coalesce"，事实是这样的吗？
coalesce与shrink space命令对比重建索引(rebuild index)有一个显著的优点:不会导致索引降级。从以上测试可以看到coalesce与shrink space compact功能完全相同；在OLTP环境中，大多数情况下我们并不希望回收索引上的空闲空间，那么coalesce或者shrink space compact(not shrink space)可以成为我们很好的选择，虽然实际操作过程中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
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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
|
SQL> conn maclean/maclean
Connected.
/* 测试使用版本10.2.0.4 * /
SQL>
select
*
from
v$VERSION;
BANNER
----------------------------------------------------------------
Oracle
Database
10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS
for
Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
/* 建立测试用表YOUYUS，高度为3 */
SQL>
drop
table
YOUYUS;
Table
dropped.
SQL>
create
table
YOUYUS
as
select
rownum t1,rpad(
'A'
,20,
'B'
) t2
from
dual
connect
by
level
<=999999;
Table
created.
SQL>
create
index
ind_youyus
on
youyus(t1,t2) nologging;
Index
created.
SQL> analyze
index
IND_YOUYUS validate structure;
Index
analyzed.
/*
大家因该很熟悉 analyze
index
.. validate structure 命令 ，实际上该命令存在一个兄弟:
analyze
index
IND_YOUYUS validate structure online，
加上online子句后validate structure可以在线操作，但该命令不会填充index_stats临时视图
*/
SQL>
set
linesize 200;
SQL>
set
linesize 200;
SQL>
select
height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13
from
index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 5376 5154 36979767 7996 9 5153 61784 8028 41283636 37041551 90
/* 可以看到IND_YOUYUS索引的基本结构，在初始状态下其block总数为5376，其中页块共5154 */
/* 我们在表上执行删除操作，均匀删除三分之一的数据 */
SQL>
delete
YOUYUS
where
mod(t1,3)=1;
333333
rows
deleted.
SQL>
commit
;
Commit
complete.
SQL> conn maclean/maclean
Connected.
SQL>
select
vs.
name
, ms.value
2
from
v$mystat ms, v$sysstat vs
3
where
vs.statistic# = ms.statistic#
4
and
vs.
name
in
(
'redo size'
,
'consistent gets'
);
NAME
VALUE
---------------------------------------------------------------- ----------
consistent gets 45
redo
size
0
SQL>
alter
index
ind_youyus
coalesce
;
Index
altered.
SQL>
select
vs.
name
, ms.value
2
from
v$mystat ms, v$sysstat vs
3
where
vs.statistic# = ms.statistic#
4
and
vs.
name
in
(
'redo size'
,
'consistent gets'
);
NAME
VALUE
---------------------------------------------------------------- ----------
consistent gets 788
redo
size
70649500
/*
coalesce
操作产生了大约67MB的redo数据 */
SQL> analyze
index
IND_YOUYUS validate structure;
Index
analyzed.
SQL>
set
linesize 200;
SQL>
select
height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13
from
index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 5376 3439 24653178 7996 9 3438 41188 8028 27570496 24694366 90
/* 可以看到执行
coalesce
(合并)操作后页块数量下降到3439，
而branch枝块和root根块的结构是不会变化的，同时coalesc命令并不释放索引上的多余空间，
但索引结构实际占用的空间BTREE_SPACE下降到了27570496 bytes */
/* 以下为此时ind_youyus索引的treedump * /
[maclean@rh2 ~]$ cat /s01/10gdb/admin/YOUYUS/udump/youyus_ora_5104.trc| \
grep
"level:"
;cat /s01/10gdb/admin/YOUYUS/udump/youyus_ora_5104.trc|grep leaf|wc -l
branch: 0x130787c 19953788 (0: nrow: 8,
level
: 2)
branch: 0x1308c41 19958849 (-1: nrow: 450,
level
: 1)
branch: 0x1308eea 19959530 (0: nrow: 447,
level
: 1)
branch: 0x1309195 19960213 (1: nrow: 447,
level
: 1)
branch: 0x130943e 19960894 (2: nrow: 447,
level
: 1)
branch: 0x13096e7 19961575 (3: nrow: 447,
level
: 1)
branch: 0x1309992 19962258 (4: nrow: 447,
level
: 1)
branch: 0x1309c3b 19962939 (5: nrow: 447,
level
: 1)
branch: 0x1309e0f 19963407 (6: nrow: 307,
level
: 1)
3439
/* 清理测试现场 */
SQL>
drop
table
YOUYUS;
Table
dropped.
SQL>
create
table
YOUYUS
as
select
rownum t1,rpad(
'A'
,20,
'B'
) t2
from
dual
connect
by
level
<=999999;
Table
created.
SQL>
create
index
ind_youyus
on
youyus(t1,t2) nologging;
Index
created.
SQL>
delete
YOUYUS
where
mod(t1,3)=1;
333333
rows
deleted.
SQL>
commit
;
Commit
complete.
SQL> conn maclean/maclean
Connected.
SQL>
select
vs.
name
, ms.value
2
from
v$mystat ms, v$sysstat vs
3
where
vs.statistic# = ms.statistic#
4
and
vs.
name
in
(
'redo size'
,
'consistent gets'
);
NAME
VALUE
---------------------------------------------------------------- ----------
consistent gets 45
redo
size
0
SQL>
alter
index
ind_youyus shrink
space
;
Index
altered.
SQL>
select
vs.
name
, ms.value
2
from
v$mystat ms, v$sysstat vs
3
where
vs.statistic# = ms.statistic#
4
and
vs.
name
in
(
'redo size'
,
'consistent gets'
);
NAME
VALUE
---------------------------------------------------------------- ----------
consistent gets 2951
redo
size
90963340
/* SHRINK
SPACE
操作产生了86MB的redo数据，多出
coalesce
时的28% */
SQL> analyze
index
IND_YOUYUS validate structure;
Index
analyzed.
SQL>
set
linesize 200;
SQL>
select
height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13
from
index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 3520 3439 24653178 7996 9 3438 41188 8028 27570496 24694366 90
/* 以下为此时ind_youyus索引的treedump * /
[maclean@rh2 ~]$ cat /s01/10gdb/admin/YOUYUS/udump/youyus_ora_5125.trc|grep
"level:"
; \
cat /s01/10gdb/admin/YOUYUS/udump/youyus_ora_5125.trc|grep leaf|wc -l
branch: 0x1309efc 19963644 (0: nrow: 8,
level
: 2)
branch: 0x130b2c1 19968705 (-1: nrow: 450,
level
: 1)
branch: 0x130b56a 19969386 (0: nrow: 447,
level
: 1)
branch: 0x130b815 19970069 (1: nrow: 447,
level
: 1)
branch: 0x130babe 19970750 (2: nrow: 447,
level
: 1)
branch: 0x130bd67 19971431 (3: nrow: 447,
level
: 1)
branch: 0x130b919 19970329 (4: nrow: 447,
level
: 1)
branch: 0x130b3bf 19968959 (5: nrow: 447,
level
: 1)
branch: 0x1309efe 19963646 (6: nrow: 307,
level
: 1)
3439
/* 索引结构与
coalesce
命令维护后相同，但shrink
space
操作释放了索引上的空闲空间 */
/* 再次清理测试现场 */
SQL>
drop
table
YOUYUS;
Table
dropped.
SQL>
create
table
YOUYUS
as
select
rownum t1,rpad(
'A'
,20,
'B'
) t2
from
dual
connect
by
level
<=999999;
Table
created.
SQL>
create
index
ind_youyus
on
youyus(t1,t2) nologging;
Index
created.
SQL>
delete
YOUYUS
where
mod(t1,3)=1;
333333
rows
deleted.
SQL>
commit
;
Commit
complete.
SQL> conn maclean/maclean
Connected.
SQL>
select
vs.
name
, ms.value
2
from
v$mystat ms, v$sysstat vs
3
where
vs.statistic# = ms.statistic#
4
and
vs.
name
in
(
'redo size'
,
'consistent gets'
);
NAME
VALUE
---------------------------------------------------------------- ----------
consistent gets 45
redo
size
0
SQL>
alter
index
ind_youyus shrink
space
compact;
Index
altered.
SQL>
select
vs.
name
, ms.value
2
from
v$mystat ms, v$sysstat vs
3
where
vs.statistic# = ms.statistic#
4
and
vs.
name
in
(
'redo size'
,
'consistent gets'
);
NAME
VALUE
---------------------------------------------------------------- ----------
consistent gets 3208
redo
size
90915424
SQL> analyze
index
IND_YOUYUS validate structure;
Index
analyzed.
SQL>
set
linesize 200;
SQL>
select
height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13
from
index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 5376 3439 24653178 7996 9 3438 41188 8028 27570496 24694366 90
/* shrink
space
compact 起到了和
coalesce
完全相同的作用，但其产生的redo仍要多于
coalesce
于28% */
|
并不是说coalesce就一定会消耗更少的资源，这需要在您的实际环境中具体测试，合适的才是最好的！
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277558
