Alter index coalesce VS shrink space

  1. 云栖社区>
  2. 博客>
  3. 正文

Alter index coalesce VS shrink space

科技小能手 2017-11-12 15:04:00 浏览823
展开阅读全文
10g中引入了对索引的shrink功能,索引shrink操作会扫描索引的页块,并且通过归并当前存在的数据将先前已删除记录的空间重新利用;很多书籍亦或者MOS的Note中都会提及SHRINK命令与早期版本中就存在的COALESCE(合并)命令具有完全相同的功能,或者说2者是完全等价的-" alter index shrink space is equivalent to coalesce",事实是这样的吗?
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与shrink space命令对比重建索引(rebuild index)有一个显著的优点:不会导致索引降级。从以上测试可以看到coalesce与shrink space compact功能完全相同;在OLTP环境中,大多数情况下我们并不希望回收索引上的空闲空间,那么coalesce或者shrink space compact(not shrink space)可以成为我们很好的选择,虽然实际操作过程中2者消耗的资源有不少差别。

并不是说coalesce就一定会消耗更少的资源,这需要在您的实际环境中具体测试,合适的才是最好的!



本文转自maclean_007 51CTO博客,原文链接:http://blog.51cto.com/maclean/1277558

网友评论

登录后评论
0/500
评论