Oracle Scratchpad

July 1, 2010

Index branches

Filed under: Indexing,Infrastructure,Troubleshooting — Jonathan Lewis @ 6:45 pm BST Jul 1,2010

Some time ago I published a little script that showed you how to read an index treedump and summarise the leaf block usage in the logical order of the index leaf blocks – allowing you to see fairly easily if the index had any areas where the blocks were poorly filled.

Here’s another  way of looking at the treedump – this time focusing on the branch blocks only. The posting is a little long, but mostly because I’ve reproduced the output from a reasonably large index so that you can see a full example from a production system.

The code is based on the same external table that I used in the previous article and queries it twice, once excluding the lowest level of  branch blocks (blevel = 1) to produce a very dense summary,  and then repeating the report but including the lowest branch blocks to give you the detail.


set pagesize 0
set linesize 80

spool read_branches

with raw_data as (
        select
                rownum                                           rn,
                instr(text_line, 'branch')                       indent,
                substr(
                        text_line,
                        instr(text_line,'level:') + 7,
                        instr(text_line,')') - ( instr(text_line,'level:') + 7)
                )                                                br_level,
                substr(
                        text_line,
                        instr(text_line,'nrow:') + 6,
                        instr(text_line,',') - (instr(text_line,'nrow:') + 6)
                )                                                row_count,
                text_line
        from
                jpl_ext
        where
                text_line like '%branch%'
)
select
        lpad(' ' , indent) ||
        decode(indent,
                1,'Root          ' || '   ',
                  'Branch level: ' || to_char(br_level,'99')
        ) ||
        '   Entries: ' || row_count
from
        raw_data
where
        br_level !=  1
union all
select
        lpad(' ' , indent) ||
        decode(indent,
                1,'Root          ' || '   ',
                  'Branch level: ' || to_char(br_level,'99')
        ) ||
        '   Entries: ' || row_count
from
        raw_data
/

spool off

As you can see from the very short summary holding just the root block and five level 2 branch blocks, there seems to be a hole in the middle of the index, with the slightly unusual pattern that the index appears to be well-packed at both the low values and the high values.

There was a rational reason for this pattern appearing – possibly the client had rebuilt the index once in the fairly distant past and had not rebuilt it since – but I can’t remember the details because I did the dump some time ago and I failed to record the explanation of the anomaly. (Rule 1 of becoming an expert: always document the anomalies – even if you can’t explain them immediately.)


 Root                Entries: 5
    Branch level:   2   Entries: 241
    Branch level:   2   Entries: 166
    Branch level:   2   Entries: 83
    Branch level:   2   Entries: 199
    Branch level:   2   Entries: 231
 Root                Entries: 5
    Branch level:   2   Entries: 241
       Branch level:   1   Entries: 268
       Branch level:   1   Entries: 258
       Branch level:   1   Entries: 271
       Branch level:   1   Entries: 144
       Branch level:   1   Entries: 122
       Branch level:   1   Entries: 132
       Branch level:   1   Entries: 114
       Branch level:   1   Entries: 236
       Branch level:   1   Entries: 237
       Branch level:   1   Entries: 134
       Branch level:   1   Entries: 113
       Branch level:   1   Entries: 127
       Branch level:   1   Entries: 132
       Branch level:   1   Entries: 142
       Branch level:   1   Entries: 113
       Branch level:   1   Entries: 255
       Branch level:   1   Entries: 137
       Branch level:   1   Entries: 129
       Branch level:   1   Entries: 131
       Branch level:   1   Entries: 150
       Branch level:   1   Entries: 265
       Branch level:   1   Entries: 255
       Branch level:   1   Entries: 255
       Branch level:   1   Entries: 120
       Branch level:   1   Entries: 144
       Branch level:   1   Entries: 254
       Branch level:   1   Entries: 254
       Branch level:   1   Entries: 254
       Branch level:   1   Entries: 258
       Branch level:   1   Entries: 139
       Branch level:   1   Entries: 117
       Branch level:   1   Entries: 255
       Branch level:   1   Entries: 257
       Branch level:   1   Entries: 118
       Branch level:   1   Entries: 147
       Branch level:   1   Entries: 119
       Branch level:   1   Entries: 136
       Branch level:   1   Entries: 146
       Branch level:   1   Entries: 115
       Branch level:   1   Entries: 114
       Branch level:   1   Entries: 144
       Branch level:   1   Entries: 250
       Branch level:   1   Entries: 251
       Branch level:   1   Entries: 135
       Branch level:   1   Entries: 123
       Branch level:   1   Entries: 61
       Branch level:   1   Entries: 116
       Branch level:   1   Entries: 71
       Branch level:   1   Entries: 112
       Branch level:   1   Entries: 68
       Branch level:   1   Entries: 105
       Branch level:   1   Entries: 109
       Branch level:   1   Entries: 102
       Branch level:   1   Entries: 72
       Branch level:   1   Entries: 80
       Branch level:   1   Entries: 120
       Branch level:   1   Entries: 63
       Branch level:   1   Entries: 94
       Branch level:   1   Entries: 53
       Branch level:   1   Entries: 68
       Branch level:   1   Entries: 158
       Branch level:   1   Entries: 83
       Branch level:   1   Entries: 46
       Branch level:   1   Entries: 117
       Branch level:   1   Entries: 62
       Branch level:   1   Entries: 75
       Branch level:   1   Entries: 101
       Branch level:   1   Entries: 114
       Branch level:   1   Entries: 135
       Branch level:   1   Entries: 3
       Branch level:   1   Entries: 170
       Branch level:   1   Entries: 51
       Branch level:   1   Entries: 145
       Branch level:   1   Entries: 163
       Branch level:   1   Entries: 110
       Branch level:   1   Entries: 127
       Branch level:   1   Entries: 124
       Branch level:   1   Entries: 130
       Branch level:   1   Entries: 139
       Branch level:   1   Entries: 139
       Branch level:   1   Entries: 124
       Branch level:   1   Entries: 146
       Branch level:   1   Entries: 125
       Branch level:   1   Entries: 118
       Branch level:   1   Entries: 153
       Branch level:   1   Entries: 130
       Branch level:   1   Entries: 125
       Branch level:   1   Entries: 132
       Branch level:   1   Entries: 129
       Branch level:   1   Entries: 142
       Branch level:   1   Entries: 116
       Branch level:   1   Entries: 144
       Branch level:   1   Entries: 113
       Branch level:   1   Entries: 144
       Branch level:   1   Entries: 110
       Branch level:   1   Entries: 124
       Branch level:   1   Entries: 139
       Branch level:   1   Entries: 130
       Branch level:   1   Entries: 126
       Branch level:   1   Entries: 102
       Branch level:   1   Entries: 65
       Branch level:   1   Entries: 72
       Branch level:   1   Entries: 140
       Branch level:   1   Entries: 102
       Branch level:   1   Entries: 114
       Branch level:   1   Entries: 77
       Branch level:   1   Entries: 95
       Branch level:   1   Entries: 97
       Branch level:   1   Entries: 143
       Branch level:   1   Entries: 87
       Branch level:   1   Entries: 82
       Branch level:   1   Entries: 86
       Branch level:   1   Entries: 144
       Branch level:   1   Entries: 41
       Branch level:   1   Entries: 136
       Branch level:   1   Entries: 60
       Branch level:   1   Entries: 125
       Branch level:   1   Entries: 92
       Branch level:   1   Entries: 87
       Branch level:   1   Entries: 119
       Branch level:   1   Entries: 101
       Branch level:   1   Entries: 127
       Branch level:   1   Entries: 89
       Branch level:   1   Entries: 77
       Branch level:   1   Entries: 99
       Branch level:   1   Entries: 72
       Branch level:   1   Entries: 93
       Branch level:   1   Entries: 155
       Branch level:   1   Entries: 138
       Branch level:   1   Entries: 150
       Branch level:   1   Entries: 116
       Branch level:   1   Entries: 114
       Branch level:   1   Entries: 149
       Branch level:   1   Entries: 151
       Branch level:   1   Entries: 118
       Branch level:   1   Entries: 146
       Branch level:   1   Entries: 121
       Branch level:   1   Entries: 145
       Branch level:   1   Entries: 114
       Branch level:   1   Entries: 138
       Branch level:   1   Entries: 117
       Branch level:   1   Entries: 136
       Branch level:   1   Entries: 116
       Branch level:   1   Entries: 139
       Branch level:   1   Entries: 111
       Branch level:   1   Entries: 135
       Branch level:   1   Entries: 110
       Branch level:   1   Entries: 245
       Branch level:   1   Entries: 110
       Branch level:   1   Entries: 137
       Branch level:   1   Entries: 136
       Branch level:   1   Entries: 113
       Branch level:   1   Entries: 113
       Branch level:   1   Entries: 137
       Branch level:   1   Entries: 142
       Branch level:   1   Entries: 114
       Branch level:   1   Entries: 113
       Branch level:   1   Entries: 141
       Branch level:   1   Entries: 126
       Branch level:   1   Entries: 128
       Branch level:   1   Entries: 133
       Branch level:   1   Entries: 120
       Branch level:   1   Entries: 138
       Branch level:   1   Entries: 113
       Branch level:   1   Entries: 115
       Branch level:   1   Entries: 138
       Branch level:   1   Entries: 111
       Branch level:   1   Entries: 144
       Branch level:   1   Entries: 127
       Branch level:   1   Entries: 126
       Branch level:   1   Entries: 254
       Branch level:   1   Entries: 135
       Branch level:   1   Entries: 122
       Branch level:   1   Entries: 142
       Branch level:   1   Entries: 115
       Branch level:   1   Entries: 248
       Branch level:   1   Entries: 123
       Branch level:   1   Entries: 132
       Branch level:   1   Entries: 139
       Branch level:   1   Entries: 115
       Branch level:   1   Entries: 254
       Branch level:   1   Entries: 123
       Branch level:   1   Entries: 138
       Branch level:   1   Entries: 117
       Branch level:   1   Entries: 138
       Branch level:   1   Entries: 250
       Branch level:   1   Entries: 249
       Branch level:   1   Entries: 133
       Branch level:   1   Entries: 116
       Branch level:   1   Entries: 255
       Branch level:   1   Entries: 136
       Branch level:   1   Entries: 113
       Branch level:   1   Entries: 253
       Branch level:   1   Entries: 255
       Branch level:   1   Entries: 137
       Branch level:   1   Entries: 128
       Branch level:   1   Entries: 122
       Branch level:   1   Entries: 134
       Branch level:   1   Entries: 115
       Branch level:   1   Entries: 141
       Branch level:   1   Entries: 145
       Branch level:   1   Entries: 118
       Branch level:   1   Entries: 134
       Branch level:   1   Entries: 121
       Branch level:   1   Entries: 255
       Branch level:   1   Entries: 255
       Branch level:   1   Entries: 140
       Branch level:   1   Entries: 122
       Branch level:   1   Entries: 142
       Branch level:   1   Entries: 120
       Branch level:   1   Entries: 137
       Branch level:   1   Entries: 127
       Branch level:   1   Entries: 130
       Branch level:   1   Entries: 113
       Branch level:   1   Entries: 258
       Branch level:   1   Entries: 123
       Branch level:   1   Entries: 132
       Branch level:   1   Entries: 135
       Branch level:   1   Entries: 121
       Branch level:   1   Entries: 113
       Branch level:   1   Entries: 145
       Branch level:   1   Entries: 129
       Branch level:   1   Entries: 145
       Branch level:   1   Entries: 151
       Branch level:   1   Entries: 121
       Branch level:   1   Entries: 118
       Branch level:   1   Entries: 138
       Branch level:   1   Entries: 143
       Branch level:   1   Entries: 117
       Branch level:   1   Entries: 145
       Branch level:   1   Entries: 121
       Branch level:   1   Entries: 127
       Branch level:   1   Entries: 138
       Branch level:   1   Entries: 252
       Branch level:   1   Entries: 254
       Branch level:   1   Entries: 136
       Branch level:   1   Entries: 117
       Branch level:   1   Entries: 141
       Branch level:   1   Entries: 121
       Branch level:   1   Entries: 125
       Branch level:   1   Entries: 132
    Branch level:   2   Entries: 166
       Branch level:   1   Entries: 140
       Branch level:   1   Entries: 114
       Branch level:   1   Entries: 117
       Branch level:   1   Entries: 138
       Branch level:   1   Entries: 174
       Branch level:   1   Entries: 125
       Branch level:   1   Entries: 144
       Branch level:   1   Entries: 121
       Branch level:   1   Entries: 258
       Branch level:   1   Entries: 255
       Branch level:   1   Entries: 116
       Branch level:   1   Entries: 147
       Branch level:   1   Entries: 113
       Branch level:   1   Entries: 136
       Branch level:   1   Entries: 113
       Branch level:   1   Entries: 100
       Branch level:   1   Entries: 139
       Branch level:   1   Entries: 118
       Branch level:   1   Entries: 79
       Branch level:   1   Entries: 109
       Branch level:   1   Entries: 88
       Branch level:   1   Entries: 120
       Branch level:   1   Entries: 86
       Branch level:   1   Entries: 86
       Branch level:   1   Entries: 99
       Branch level:   1   Entries: 127
       Branch level:   1   Entries: 86
       Branch level:   1   Entries: 110
       Branch level:   1   Entries: 79
       Branch level:   1   Entries: 104
       Branch level:   1   Entries: 106
       Branch level:   1   Entries: 125
       Branch level:   1   Entries: 118
       Branch level:   1   Entries: 132
       Branch level:   1   Entries: 156
       Branch level:   1   Entries: 121
       Branch level:   1   Entries: 146
       Branch level:   1   Entries: 58
       Branch level:   1   Entries: 112
       Branch level:   1   Entries: 152
       Branch level:   1   Entries: 126
       Branch level:   1   Entries: 133
       Branch level:   1   Entries: 119
       Branch level:   1   Entries: 150
       Branch level:   1   Entries: 133
       Branch level:   1   Entries: 123
       Branch level:   1   Entries: 125
       Branch level:   1   Entries: 135
       Branch level:   1   Entries: 143
       Branch level:   1   Entries: 172
       Branch level:   1   Entries: 123
       Branch level:   1   Entries: 168
       Branch level:   1   Entries: 161
       Branch level:   1   Entries: 141
       Branch level:   1   Entries: 144
       Branch level:   1   Entries: 141
       Branch level:   1   Entries: 131
       Branch level:   1   Entries: 122
       Branch level:   1   Entries: 183
       Branch level:   1   Entries: 149
       Branch level:   1   Entries: 118
       Branch level:   1   Entries: 132
       Branch level:   1   Entries: 141
       Branch level:   1   Entries: 133
       Branch level:   1   Entries: 156
       Branch level:   1   Entries: 165
       Branch level:   1   Entries: 142
       Branch level:   1   Entries: 135
       Branch level:   1   Entries: 160
       Branch level:   1   Entries: 180
       Branch level:   1   Entries: 131
       Branch level:   1   Entries: 139
       Branch level:   1   Entries: 116
       Branch level:   1   Entries: 115
       Branch level:   1   Entries: 183
       Branch level:   1   Entries: 75
       Branch level:   1   Entries: 155
       Branch level:   1   Entries: 90
       Branch level:   1   Entries: 106
       Branch level:   1   Entries: 91
       Branch level:   1   Entries: 109
       Branch level:   1   Entries: 84
       Branch level:   1   Entries: 151
       Branch level:   1   Entries: 154
       Branch level:   1   Entries: 112
       Branch level:   1   Entries: 133
       Branch level:   1   Entries: 124
       Branch level:   1   Entries: 125
       Branch level:   1   Entries: 130
       Branch level:   1   Entries: 135
       Branch level:   1   Entries: 198
       Branch level:   1   Entries: 121
       Branch level:   1   Entries: 136
       Branch level:   1   Entries: 143
       Branch level:   1   Entries: 108
       Branch level:   1   Entries: 142
       Branch level:   1   Entries: 126
       Branch level:   1   Entries: 136
       Branch level:   1   Entries: 145
       Branch level:   1   Entries: 102
       Branch level:   1   Entries: 147
       Branch level:   1   Entries: 92
       Branch level:   1   Entries: 113
       Branch level:   1   Entries: 128
       Branch level:   1   Entries: 142
       Branch level:   1   Entries: 139
       Branch level:   1   Entries: 144
       Branch level:   1   Entries: 107
       Branch level:   1   Entries: 149
       Branch level:   1   Entries: 146
       Branch level:   1   Entries: 120
       Branch level:   1   Entries: 130
       Branch level:   1   Entries: 127
       Branch level:   1   Entries: 114
       Branch level:   1   Entries: 131
       Branch level:   1   Entries: 55
       Branch level:   1   Entries: 82
       Branch level:   1   Entries: 93
       Branch level:   1   Entries: 151
       Branch level:   1   Entries: 245
       Branch level:   1   Entries: 249
       Branch level:   1   Entries: 133
       Branch level:   1   Entries: 156
       Branch level:   1   Entries: 152
       Branch level:   1   Entries: 112
       Branch level:   1   Entries: 90
       Branch level:   1   Entries: 168
       Branch level:   1   Entries: 125
       Branch level:   1   Entries: 256
       Branch level:   1   Entries: 214
       Branch level:   1   Entries: 138
       Branch level:   1   Entries: 138
       Branch level:   1   Entries: 212
       Branch level:   1   Entries: 173
       Branch level:   1   Entries: 31
       Branch level:   1   Entries: 160
       Branch level:   1   Entries: 134
       Branch level:   1   Entries: 92
       Branch level:   1   Entries: 132
       Branch level:   1   Entries: 126
       Branch level:   1   Entries: 141
       Branch level:   1   Entries: 150
       Branch level:   1   Entries: 148
       Branch level:   1   Entries: 161
       Branch level:   1   Entries: 174
       Branch level:   1   Entries: 195
       Branch level:   1   Entries: 112
       Branch level:   1   Entries: 103
       Branch level:   1   Entries: 197
       Branch level:   1   Entries: 186
       Branch level:   1   Entries: 163
       Branch level:   1   Entries: 185
       Branch level:   1   Entries: 158
       Branch level:   1   Entries: 253
       Branch level:   1   Entries: 183
       Branch level:   1   Entries: 10
       Branch level:   1   Entries: 2
       Branch level:   1   Entries: 8
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 14
       Branch level:   1   Entries: 3
    Branch level:   2   Entries: 83
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 30
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 4
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 3
       Branch level:   1   Entries: 174
       Branch level:   1   Entries: 206
       Branch level:   1   Entries: 203
       Branch level:   1   Entries: 183
       Branch level:   1   Entries: 249
       Branch level:   1   Entries: 168
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 13
       Branch level:   1   Entries: 32
       Branch level:   1   Entries: 15
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 38
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 25
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 2
       Branch level:   1   Entries: 248
       Branch level:   1   Entries: 238
       Branch level:   1   Entries: 211
       Branch level:   1   Entries: 238
       Branch level:   1   Entries: 212
       Branch level:   1   Entries: 194
       Branch level:   1   Entries: 188
       Branch level:   1   Entries: 220
       Branch level:   1   Entries: 249
       Branch level:   1   Entries: 153
       Branch level:   1   Entries: 68
       Branch level:   1   Entries: 118
       Branch level:   1   Entries: 9
       Branch level:   1   Entries: 136
       Branch level:   1   Entries: 151
       Branch level:   1   Entries: 120
       Branch level:   1   Entries: 141
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 3
       Branch level:   1   Entries: 56
       Branch level:   1   Entries: 132
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 68
       Branch level:   1   Entries: 53
       Branch level:   1   Entries: 7
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 21
       Branch level:   1   Entries: 26
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 4
       Branch level:   1   Entries: 11
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 3
       Branch level:   1   Entries: 81
       Branch level:   1   Entries: 67
       Branch level:   1   Entries: 15
       Branch level:   1   Entries: 89
       Branch level:   1   Entries: 80
       Branch level:   1   Entries: 124
       Branch level:   1   Entries: 114
       Branch level:   1   Entries: 127
       Branch level:   1   Entries: 130
       Branch level:   1   Entries: 133
    Branch level:   2   Entries: 199
       Branch level:   1   Entries: 32
       Branch level:   1   Entries: 54
       Branch level:   1   Entries: 23
       Branch level:   1   Entries: 201
       Branch level:   1   Entries: 55
       Branch level:   1   Entries: 8
       Branch level:   1   Entries: 82
       Branch level:   1   Entries: 63
       Branch level:   1   Entries: 88
       Branch level:   1   Entries: 85
       Branch level:   1   Entries: 109
       Branch level:   1   Entries: 65
       Branch level:   1   Entries: 124
       Branch level:   1   Entries: 142
       Branch level:   1   Entries: 220
       Branch level:   1   Entries: 155
       Branch level:   1   Entries: 123
       Branch level:   1   Entries: 74
       Branch level:   1   Entries: 82
       Branch level:   1   Entries: 45
       Branch level:   1   Entries: 191
       Branch level:   1   Entries: 125
       Branch level:   1   Entries: 201
       Branch level:   1   Entries: 228
       Branch level:   1   Entries: 14
       Branch level:   1   Entries: 77
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 285
       Branch level:   1   Entries: 204
       Branch level:   1   Entries: 206
       Branch level:   1   Entries: 143
       Branch level:   1   Entries: 14
       Branch level:   1   Entries: 72
       Branch level:   1   Entries: 160
       Branch level:   1   Entries: 53
       Branch level:   1   Entries: 73
       Branch level:   1   Entries: 50
       Branch level:   1   Entries: 102
       Branch level:   1   Entries: 49
       Branch level:   1   Entries: 116
       Branch level:   1   Entries: 78
       Branch level:   1   Entries: 23
       Branch level:   1   Entries: 97
       Branch level:   1   Entries: 130
       Branch level:   1   Entries: 92
       Branch level:   1   Entries: 135
       Branch level:   1   Entries: 20
       Branch level:   1   Entries: 79
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 71
       Branch level:   1   Entries: 164
       Branch level:   1   Entries: 15
       Branch level:   1   Entries: 217
       Branch level:   1   Entries: 87
       Branch level:   1   Entries: 141
       Branch level:   1   Entries: 69
       Branch level:   1   Entries: 62
       Branch level:   1   Entries: 189
       Branch level:   1   Entries: 70
       Branch level:   1   Entries: 42
       Branch level:   1   Entries: 107
       Branch level:   1   Entries: 160
       Branch level:   1   Entries: 118
       Branch level:   1   Entries: 127
       Branch level:   1   Entries: 182
       Branch level:   1   Entries: 68
       Branch level:   1   Entries: 150
       Branch level:   1   Entries: 134
       Branch level:   1   Entries: 120
       Branch level:   1   Entries: 63
       Branch level:   1   Entries: 85
       Branch level:   1   Entries: 62
       Branch level:   1   Entries: 116
       Branch level:   1   Entries: 62
       Branch level:   1   Entries: 103
       Branch level:   1   Entries: 241
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 1
       Branch level:   1   Entries: 8
       Branch level:   1   Entries: 54
       Branch level:   1   Entries: 68
       Branch level:   1   Entries: 65
       Branch level:   1   Entries: 18
       Branch level:   1   Entries: 62
       Branch level:   1   Entries: 63
       Branch level:   1   Entries: 46
       Branch level:   1   Entries: 54
       Branch level:   1   Entries: 56
       Branch level:   1   Entries: 49
       Branch level:   1   Entries: 50
       Branch level:   1   Entries: 14
       Branch level:   1   Entries: 50
       Branch level:   1   Entries: 130
       Branch level:   1   Entries: 166
       Branch level:   1   Entries: 86
       Branch level:   1   Entries: 197
       Branch level:   1   Entries: 83
       Branch level:   1   Entries: 103
       Branch level:   1   Entries: 37
       Branch level:   1   Entries: 87
       Branch level:   1   Entries: 73
       Branch level:   1   Entries: 113
       Branch level:   1   Entries: 84
       Branch level:   1   Entries: 17
       Branch level:   1   Entries: 117
       Branch level:   1   Entries: 70
       Branch level:   1   Entries: 82
       Branch level:   1   Entries: 53
       Branch level:   1   Entries: 93
       Branch level:   1   Entries: 132
       Branch level:   1   Entries: 84
       Branch level:   1   Entries: 46
       Branch level:   1   Entries: 121
       Branch level:   1   Entries: 59
       Branch level:   1   Entries: 89
       Branch level:   1   Entries: 54
       Branch level:   1   Entries: 104
       Branch level:   1   Entries: 91
       Branch level:   1   Entries: 53
       Branch level:   1   Entries: 133
       Branch level:   1   Entries: 34
       Branch level:   1   Entries: 96
       Branch level:   1   Entries: 62
       Branch level:   1   Entries: 121
       Branch level:   1   Entries: 82
       Branch level:   1   Entries: 94
       Branch level:   1   Entries: 59
       Branch level:   1   Entries: 93
       Branch level:   1   Entries: 101
       Branch level:   1   Entries: 44
       Branch level:   1   Entries: 84
       Branch level:   1   Entries: 129
       Branch level:   1   Entries: 59
       Branch level:   1   Entries: 83
       Branch level:   1   Entries: 59
       Branch level:   1   Entries: 87
       Branch level:   1   Entries: 77
       Branch level:   1   Entries: 123
       Branch level:   1   Entries: 145
       Branch level:   1   Entries: 126
       Branch level:   1   Entries: 131
       Branch level:   1   Entries: 109
       Branch level:   1   Entries: 172
       Branch level:   1   Entries: 214
       Branch level:   1   Entries: 161
       Branch level:   1   Entries: 120
       Branch level:   1   Entries: 143
       Branch level:   1   Entries: 161
       Branch level:   1   Entries: 152
       Branch level:   1   Entries: 214
       Branch level:   1   Entries: 149
       Branch level:   1   Entries: 171
       Branch level:   1   Entries: 197
       Branch level:   1   Entries: 239
       Branch level:   1   Entries: 143
       Branch level:   1   Entries: 187
       Branch level:   1   Entries: 185
       Branch level:   1   Entries: 135
       Branch level:   1   Entries: 240
       Branch level:   1   Entries: 115
       Branch level:   1   Entries: 116
       Branch level:   1   Entries: 73
       Branch level:   1   Entries: 98
       Branch level:   1   Entries: 99
       Branch level:   1   Entries: 94
       Branch level:   1   Entries: 98
       Branch level:   1   Entries: 246
       Branch level:   1   Entries: 222
       Branch level:   1   Entries: 76
       Branch level:   1   Entries: 31
       Branch level:   1   Entries: 107
       Branch level:   1   Entries: 138
       Branch level:   1   Entries: 166
       Branch level:   1   Entries: 131
       Branch level:   1   Entries: 112
       Branch level:   1   Entries: 53
       Branch level:   1   Entries: 117
       Branch level:   1   Entries: 98
       Branch level:   1   Entries: 60
       Branch level:   1   Entries: 75
       Branch level:   1   Entries: 102
       Branch level:   1   Entries: 62
       Branch level:   1   Entries: 42
       Branch level:   1   Entries: 136
       Branch level:   1   Entries: 60
       Branch level:   1   Entries: 79
       Branch level:   1   Entries: 96
       Branch level:   1   Entries: 43
       Branch level:   1   Entries: 82
       Branch level:   1   Entries: 124
       Branch level:   1   Entries: 85
       Branch level:   1   Entries: 78
       Branch level:   1   Entries: 67
       Branch level:   1   Entries: 10
       Branch level:   1   Entries: 136
       Branch level:   1   Entries: 183
       Branch level:   1   Entries: 38
       Branch level:   1   Entries: 208
    Branch level:   2   Entries: 231
       Branch level:   1   Entries: 90
       Branch level:   1   Entries: 74
       Branch level:   1   Entries: 67
       Branch level:   1   Entries: 70
       Branch level:   1   Entries: 129
       Branch level:   1   Entries: 154
       Branch level:   1   Entries: 41
       Branch level:   1   Entries: 75
       Branch level:   1   Entries: 145
       Branch level:   1   Entries: 140
       Branch level:   1   Entries: 56
       Branch level:   1   Entries: 112
       Branch level:   1   Entries: 57
       Branch level:   1   Entries: 100
       Branch level:   1   Entries: 72
       Branch level:   1   Entries: 83
       Branch level:   1   Entries: 91
       Branch level:   1   Entries: 58
       Branch level:   1   Entries: 136
       Branch level:   1   Entries: 2
       Branch level:   1   Entries: 81
       Branch level:   1   Entries: 188
       Branch level:   1   Entries: 73
       Branch level:   1   Entries: 83
       Branch level:   1   Entries: 22
       Branch level:   1   Entries: 131
       Branch level:   1   Entries: 63
       Branch level:   1   Entries: 58
       Branch level:   1   Entries: 132
       Branch level:   1   Entries: 68
       Branch level:   1   Entries: 10
       Branch level:   1   Entries: 139
       Branch level:   1   Entries: 55
       Branch level:   1   Entries: 57
       Branch level:   1   Entries: 104
       Branch level:   1   Entries: 2
       Branch level:   1   Entries: 100
       Branch level:   1   Entries: 185
       Branch level:   1   Entries: 123
       Branch level:   1   Entries: 57
       Branch level:   1   Entries: 124
       Branch level:   1   Entries: 145
       Branch level:   1   Entries: 46
       Branch level:   1   Entries: 80
       Branch level:   1   Entries: 79
       Branch level:   1   Entries: 74
       Branch level:   1   Entries: 105
       Branch level:   1   Entries: 54
       Branch level:   1   Entries: 105
       Branch level:   1   Entries: 64
       Branch level:   1   Entries: 113
       Branch level:   1   Entries: 202
       Branch level:   1   Entries: 119
       Branch level:   1   Entries: 200
       Branch level:   1   Entries: 206
       Branch level:   1   Entries: 199
       Branch level:   1   Entries: 132
       Branch level:   1   Entries: 154
       Branch level:   1   Entries: 128
       Branch level:   1   Entries: 121
       Branch level:   1   Entries: 119
       Branch level:   1   Entries: 122
       Branch level:   1   Entries: 93
       Branch level:   1   Entries: 134
       Branch level:   1   Entries: 216
       Branch level:   1   Entries: 127
       Branch level:   1   Entries: 167
       Branch level:   1   Entries: 114
       Branch level:   1   Entries: 140
       Branch level:   1   Entries: 139
       Branch level:   1   Entries: 72
       Branch level:   1   Entries: 100
       Branch level:   1   Entries: 124
       Branch level:   1   Entries: 71
       Branch level:   1   Entries: 138
       Branch level:   1   Entries: 135
       Branch level:   1   Entries: 121
       Branch level:   1   Entries: 155
       Branch level:   1   Entries: 211
       Branch level:   1   Entries: 167
       Branch level:   1   Entries: 127
       Branch level:   1   Entries: 116
       Branch level:   1   Entries: 107
       Branch level:   1   Entries: 147
       Branch level:   1   Entries: 113
       Branch level:   1   Entries: 220
       Branch level:   1   Entries: 133
       Branch level:   1   Entries: 127
       Branch level:   1   Entries: 141
       Branch level:   1   Entries: 144
       Branch level:   1   Entries: 162
       Branch level:   1   Entries: 148
       Branch level:   1   Entries: 244
       Branch level:   1   Entries: 144
       Branch level:   1   Entries: 123
       Branch level:   1   Entries: 144
       Branch level:   1   Entries: 145
       Branch level:   1   Entries: 184
       Branch level:   1   Entries: 118
       Branch level:   1   Entries: 160
       Branch level:   1   Entries: 97
       Branch level:   1   Entries: 44
       Branch level:   1   Entries: 145
       Branch level:   1   Entries: 131
       Branch level:   1   Entries: 5
       Branch level:   1   Entries: 169
       Branch level:   1   Entries: 74
       Branch level:   1   Entries: 92
       Branch level:   1   Entries: 100
       Branch level:   1   Entries: 52
       Branch level:   1   Entries: 74
       Branch level:   1   Entries: 23
       Branch level:   1   Entries: 147
       Branch level:   1   Entries: 44
       Branch level:   1   Entries: 37
       Branch level:   1   Entries: 136
       Branch level:   1   Entries: 44
       Branch level:   1   Entries: 100
       Branch level:   1   Entries: 134
       Branch level:   1   Entries: 82
       Branch level:   1   Entries: 97
       Branch level:   1   Entries: 60
       Branch level:   1   Entries: 146
       Branch level:   1   Entries: 108
       Branch level:   1   Entries: 31
       Branch level:   1   Entries: 97
       Branch level:   1   Entries: 113
       Branch level:   1   Entries: 64
       Branch level:   1   Entries: 115
       Branch level:   1   Entries: 72
       Branch level:   1   Entries: 134
       Branch level:   1   Entries: 80
       Branch level:   1   Entries: 36
       Branch level:   1   Entries: 151
       Branch level:   1   Entries: 15
       Branch level:   1   Entries: 152
       Branch level:   1   Entries: 38
       Branch level:   1   Entries: 107
       Branch level:   1   Entries: 95
       Branch level:   1   Entries: 73
       Branch level:   1   Entries: 189
       Branch level:   1   Entries: 143
       Branch level:   1   Entries: 182
       Branch level:   1   Entries: 106
       Branch level:   1   Entries: 101
       Branch level:   1   Entries: 70
       Branch level:   1   Entries: 105
       Branch level:   1   Entries: 82
       Branch level:   1   Entries: 177
       Branch level:   1   Entries: 211
       Branch level:   1   Entries: 155
       Branch level:   1   Entries: 163
       Branch level:   1   Entries: 130
       Branch level:   1   Entries: 157
       Branch level:   1   Entries: 218
       Branch level:   1   Entries: 116
       Branch level:   1   Entries: 173
       Branch level:   1   Entries: 118
       Branch level:   1   Entries: 126
       Branch level:   1   Entries: 137
       Branch level:   1   Entries: 125
       Branch level:   1   Entries: 132
       Branch level:   1   Entries: 148
       Branch level:   1   Entries: 118
       Branch level:   1   Entries: 177
       Branch level:   1   Entries: 124
       Branch level:   1   Entries: 217
       Branch level:   1   Entries: 125
       Branch level:   1   Entries: 170
       Branch level:   1   Entries: 206
       Branch level:   1   Entries: 215
       Branch level:   1   Entries: 231
       Branch level:   1   Entries: 122
       Branch level:   1   Entries: 140
       Branch level:   1   Entries: 229
       Branch level:   1   Entries: 168
       Branch level:   1   Entries: 189
       Branch level:   1   Entries: 99
       Branch level:   1   Entries: 73
       Branch level:   1   Entries: 79
       Branch level:   1   Entries: 71
       Branch level:   1   Entries: 64
       Branch level:   1   Entries: 76
       Branch level:   1   Entries: 68
       Branch level:   1   Entries: 67
       Branch level:   1   Entries: 76
       Branch level:   1   Entries: 80
       Branch level:   1   Entries: 74
       Branch level:   1   Entries: 156
       Branch level:   1   Entries: 178
       Branch level:   1   Entries: 162
       Branch level:   1   Entries: 306
       Branch level:   1   Entries: 84
       Branch level:   1   Entries: 82
       Branch level:   1   Entries: 74
       Branch level:   1   Entries: 86
       Branch level:   1   Entries: 81
       Branch level:   1   Entries: 124
       Branch level:   1   Entries: 144
       Branch level:   1   Entries: 120
       Branch level:   1   Entries: 124
       Branch level:   1   Entries: 161
       Branch level:   1   Entries: 88
       Branch level:   1   Entries: 79
       Branch level:   1   Entries: 72
       Branch level:   1   Entries: 62
       Branch level:   1   Entries: 77
       Branch level:   1   Entries: 79
       Branch level:   1   Entries: 73
       Branch level:   1   Entries: 79
       Branch level:   1   Entries: 70
       Branch level:   1   Entries: 124
       Branch level:   1   Entries: 115
       Branch level:   1   Entries: 72
       Branch level:   1   Entries: 84
       Branch level:   1   Entries: 69
       Branch level:   1   Entries: 94
       Branch level:   1   Entries: 70
       Branch level:   1   Entries: 127
       Branch level:   1   Entries: 142
       Branch level:   1   Entries: 146
       Branch level:   1   Entries: 130
       Branch level:   1   Entries: 256
       Branch level:   1   Entries: 237
       Branch level:   1   Entries: 178
       Branch level:   1   Entries: 177
       Branch level:   1   Entries: 234
       Branch level:   1   Entries: 188
       Branch level:   1   Entries: 230
       Branch level:   1   Entries: 170
       Branch level:   1   Entries: 16

932 rows selected.

As you can then see in the detail, there are a few patches where the level 1 branch blocks list just one or two leaf blocks. This isn’t necessarily anything to worry about – in a few days time, perhaps, the last leaf blocks referenced by those branches may become empty and get removed, at which point the branch block will also be removed from the structure.

The one thing you might consider in this specific case (apart from why there’s a “hole in the middle”) is whether the total number of entries in the level 1 branch blocks could be reduced so much that they could fit into far fewer branch blocks, and whether the resulting number of branch blocks would fit into a single root block. If so then you would be able to reduce the height of this index by rebuilding it; and if the index is frequently used for high-precision access, that reduction in height might save some latch and CPU activity during queries. (As a guideline, a single branch block in this index looks as if it might handle a maximum of something like 260 – 270 pointers – the numbers in the leftmost blocks of the index might be special cases, but could be giving us roughly the right idea)

Of course, even if the rebuild seems to make sense, and leaves a little room for growth before the root block splits again, you still want to keep an eye on this index and watch how its structure evolves after the rebuild.

*** Footnote: like many of the scripts I’ve published on this blog, the SQL shown here is “good enough” for me to use because I know how it works and I won’t have to use it often. It’s not intended to be a bullet-proof program in a “productised” state.

1 Comment »

  1. [...] of reading a treedump and using an analytic function to “draw a picture” of an index. Reporting Branch blocks: Similar to “drawing an index”, but listing just the branch blocks in index [...]

    Pingback by Simple scripts « Oracle Scratchpad — July 3, 2010 @ 7:38 am BST Jul 3,2010 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,268 other followers