(1) Decreasing the number of gets in the index range scan operation due to indexes are more compressed
(2) Increasing the chances of caching, thus reducing the need to wait for db file sequential read.
For example, let's say, index entry 1 is in block 1 and entry 2 is in block 2 before rebuild, so Oracle needs to read block1 and 2 to the buffer cache to delete them; while after rebuild both entry 1 and entry 2 could be in block 1, so Oracle needs to read block 1 once and can work on two entries.
Among those 5 tables, the top-most expensive delete is the one from a table called ORDER_TRANS. There is an index called ORDER_TRANS_IX4 of this table is 16GB after rebuild as compared to 150GB before rebuild. Why is the index so sparse? The index is on the LAST_MODIFIED_DATE column of the table. This is a typical right-hand index, i.e. new index key entries are added to the right hand size of the index tree. The key value is monotonically increasing, like a sequence-based PK. If assuming a leaf block of the index can hold 360 index entries, 99.9% blocks (hold <=180 entries) are >50% empty; 97.5% blocks (hold <=90 entries) are >75% empty. (see appendix) .The reasons that why especially right-hand index can grow bigger are described in a Jonathan Lewis's blog post. In my particular case, I believe the problems of the archival procedure itself contribute to the sparsity of the index greatly.
The candidate orders that are eligible for archiving is based on certain business logic. However I found that there are very old orders. e.g. competed in 2004, 2005, that are not selected out for archiving. I confirmed with a lead developer who called those orders "fallout" that those orders should be updated so that they could become eligible for archiving long time ago. In addition, the orders are not selected based on the increasing order of its completion date or cancel date. We can imagine that if we really archive old orders based on their completion or cancel date, i.e, the older the earlier to be archived, we won't have that sparse index in the first place, because left-hand size leaf block can become empty much sooner and easily and thus reused.
In summary, it appears that rebuild index improves our archival procedure performance; however, we may want to look it in the other way around. If the old orders are really archived based on common sense, we probably don't need to rebuild index at all.
Appendix - How the leaf blocks of ORDER_TRANS_IX4 are used based on JL's script. ( Sample 5% blocks)
ROWS_PER_BLOCK BLOCKS ROW_CT CUMULATIVE_BLOCKS
-------------- ---------- ---------- -----------------
1 16232 16232 16232
2 14696 29392 30928
3 13739 41217 44667
4 13840 55360 58507
5 13316 66580 71823
6 13859 83154 85682
7 13787 96509 99469
8 13842 110736 113311
9 13937 125433 127248
10 14127 141270 141375
11 14244 156684 155619
12 14528 174336 170147
13 14566 189358 184713
14 15005 210070 199718
15 15150 227250 214868
16 15492 247872 230360
17 15868 269756 246228
18 16015 288270 262243
19 16026 304494 278269
20 16217 324340 294486
21 16106 338226 310592
22 16121 354662 326713
23 16257 373911 342970
24 15860 380640 358830
25 15912 397800 374742
26 15920 413920 390662
27 15859 428193 406521
28 15462 432936 421983
29 15288 443352 437271
30 15095 452850 452366
31 15346 475726 467712
32 15000 480000 482712
33 14832 489456 497544
34 14719 500446 512263
35 14147 495145 526410
36 13925 501300 540335
37 13671 505827 554006
38 13452 511176 567458
39 13278 517842 580736
40 13109 524360 593845
41 13040 534640 606885
42 12801 537642 619686
43 12338 530534 632024
44 11986 527384 644010
45 11732 527940 655742
46 11123 511658 666865
47 11126 522922 677991
48 10896 523008 688887
49 10546 516754 699433
50 10178 508900 709611
51 9644 491844 719255
52 9593 498836 728848
53 9208 488024 738056
54 9009 486486 747065
55 8421 463155 755486
56 8393 470008 763879
57 7992 455544 771871
58 7651 443758 779522
59 7329 432411 786851
60 6941 416460 793792
61 6662 406382 800454
62 6526 404612 806980
63 6320 398160 813300
64 5864 375296 819164
65 5687 369655 824851
66 5334 352044 830185
67 5182 347194 835367
68 4953 336804 840320
69 4728 326232 845048
70 4379 306530 849427
71 4219 299549 853646
72 4124 296928 857770
73 3769 275137 861539
74 3737 276538 865276
75 3553 266475 868829
76 3268 248368 872097
77 3102 238854 875199
78 3010 234780 878209
79 2860 225940 881069
80 2608 208640 883677
81 2552 206712 886229
82 2408 197456 888637
83 2238 185754 890875
84 2236 187824 893111
85 1989 169065 895100
86 1883 161938 896983
87 1737 151119 898720
88 1705 150040 900425
89 1627 144803 902052
90 1479 133110 903531
91 1447 131677 904978
92 1314 120888 906292
93 1316 122388 907608
94 1207 113458 908815
95 1258 119510 910073
96 1065 102240 911138
97 1049 101753 912187
98 974 95452 913161
99 927 91773 914088
100 919 91900 915007
101 877 88577 915884
102 828 84456 916712
103 794 81782 917506
104 666 69264 918172
105 635 66675 918807
106 617 65402 919424
107 549 58743 919973
108 458 49464 920431
109 475 51775 920906
110 292 32120 921198
111 243 26973 921441
112 244 27328 921685
113 201 22713 921886
114 219 24966 922105
115 169 19435 922274
116 150 17400 922424
117 167 19539 922591
118 177 20886 922768
119 128 15232 922896
120 135 16200 923031
121 140 16940 923171
122 134 16348 923305
123 130 15990 923435
124 99 12276 923534
125 107 13375 923641
126 104 13104 923745
127 84 10668 923829
128 106 13568 923935
129 86 11094 924021
130 68 8840 924089
131 97 12707 924186
132 71 9372 924257
133 71 9443 924328
134 61 8174 924389
135 72 9720 924461
136 82 11152 924543
137 48 6576 924591
138 72 9936 924663
139 64 8896 924727
140 58 8120 924785
141 41 5781 924826
142 41 5822 924867
143 35 5005 924902
144 45 6480 924947
145 39 5655 924986
146 37 5402 925023
147 32 4704 925055
148 39 5772 925094
149 26 3874 925120
150 25 3750 925145
151 38 5738 925183
152 33 5016 925216
153 34 5202 925250
154 36 5544 925286
155 33 5115 925319
156 29 4524 925348
157 33 5181 925381
158 25 3950 925406
159 22 3498 925428
160 34 5440 925462
161 22 3542 925484
162 21 3402 925505
163 21 3423 925526
164 22 3608 925548
165 21 3465 925569
166 25 4150 925594
167 19 3173 925613
168 17 2856 925630
169 16 2704 925646
170 15 2550 925661
171 17 2907 925678
172 9 1548 925687
173 9 1557 925696
174 17 2958 925713
175 13 2275 925726
176 16 2816 925742
177 13 2301 925755
178 14 2492 925769
179 10 1790 925779
180 7 1260 925786
181 7 1267 925793
182 6 1092 925799
183 9 1647 925808
184 7 1288 925815
185 7 1295 925822
186 9 1674 925831
187 6 1122 925837
188 8 1504 925845
189 5 945 925850
190 7 1330 925857
191 5 955 925862
192 9 1728 925871
193 6 1158 925877
194 5 970 925882
195 8 1560 925890
196 8 1568 925898
197 9 1773 925907
198 6 1188 925913
199 5 995 925918
200 3 600 925921
201 2 402 925923
202 6 1212 925929
203 5 1015 925934
204 5 1020 925939
205 4 820 925943
206 5 1030 925948
207 3 621 925951
209 1 209 925952
210 5 1050 925957
211 3 633 925960
212 3 636 925963
213 3 639 925966
214 4 856 925970
215 3 645 925973
216 1 216 925974
217 2 434 925976
218 4 872 925980
219 3 657 925983
220 2 440 925985
221 2 442 925987
222 2 444 925989
223 4 892 925993
224 2 448 925995
225 2 450 925997
226 2 452 925999
227 2 454 926001
228 1 228 926002
229 4 916 926006
230 5 1150 926011
231 2 462 926013
232 3 696 926016
233 2 466 926018
234 1 234 926019
235 5 1175 926024
236 3 708 926027
237 3 711 926030
239 3 717 926033
240 4 960 926037
241 4 964 926041
242 1 242 926042
243 2 486 926044
244 5 1220 926049
245 3 735 926052
246 2 492 926054
248 1 248 926055
249 1 249 926056
250 2 500 926058
252 4 1008 926062
255 4 1020 926066
256 4 1024 926070
257 2 514 926072
258 1 258 926073
259 4 1036 926077
261 1 261 926078
262 2 524 926080
263 1 263 926081
264 2 528 926083
265 1 265 926084
266 2 532 926086
267 4 1068 926090
268 2 536 926092
269 2 538 926094
271 1 271 926095
272 1 272 926096
273 3 819 926099
275 2 550 926101
277 1 277 926102
278 1 278 926103
279 1 279 926104
280 2 560 926106
281 1 281 926107
283 1 283 926108
284 3 852 926111
285 1 285 926112
286 3 858 926115
288 2 576 926117
289 1 289 926118
290 1 290 926119
291 2 582 926121
292 3 876 926124
293 3 879 926127
294 1 294 926128
295 1 295 926129
296 1 296 926130
297 2 594 926132
298 2 596 926134
299 1 299 926135
301 1 301 926136
302 4 1208 926140
305 1 305 926141
306 1 306 926142
308 1 308 926143
309 3 927 926146
310 1 310 926147
313 1 313 926148
314 1 314 926149
315 2 630 926151
317 1 317 926152
318 2 636 926154
319 1 319 926155
320 2 640 926157
321 2 642 926159
322 3 966 926162
323 2 646 926164
324 2 648 926166
325 1 325 926167
326 1 326 926168
327 2 654 926170
328 3 984 926173
329 3 987 926176
330 3 990 926179
331 2 662 926181
332 2 664 926183
333 2 666 926185
334 2 668 926187
335 2 670 926189
336 1 336 926190
337 2 674 926192
338 3 1014 926195
340 3 1020 926198
341 3 1023 926201
342 1 342 926202
343 3 1029 926205
344 6 2064 926211
345 4 1380 926215
346 2 692 926217
347 2 694 926219
348 4 1392 926223
349 4 1396 926227
350 5 1750 926232
351 3 1053 926235
352 4 1408 926239
354 3 1062 926242
355 5 1775 926247
356 5 1780 926252
357 4 1428 926256
358 6 2148 926262
359 2 718 926264
360 6 2160 926270
361 9 3249 926279
362 6 2172 926285
363 5 1815 926290
364 6 2184 926296
365 9 3285 926305
366 11 4026 926316
367 43 15781 926359
---------- ----------
sum 926359 32433921
