Alter index coalesce VS shrink space

简介:
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
相关文章
|
2月前
|
存储 索引
mysqldump got error 1812 tablespace is missing for table when using lock tables
mysqldump got error 1812 tablespace is missing for table when using lock tables
|
2月前
|
算法 C++ 容器
【C++算法】is_partitioned、partition_copy和partition_point
【C++算法】is_partitioned、partition_copy和partition_point
|
SQL 关系型数据库
[WorkLog] InnoDB Faster truncate/drop table space
这个系列, 介绍upstream 一些有意思的worklog **问题** 在InnoDB 现有的版本里面, 如果一个table space 被truncated 或者 drop 的时候, 比如有一个连接创建了临时表, 连接断开以后, 对应的临时表都需要进行drop 操作. InnoDB 是需要将该tablespace 对应的所有的page 从LRU/FLUSH li
429 0
|
机器学习/深度学习 C++ Go