Oracle blog

How to predict Index blevel and when to rebuild.

Determining Index blevel's

Depth of an index(blevel plus one) would determine the minimum number of consistent gets required for Index lookups in any joins and in index range scans. In nested loop joins, Oracle would always read index root block into process PGA memory and hence one less block to read from an index in every loop iteration. For queries like "where x between :a and :b" Oracle would always do leaf block level walk throughs, hence caching any index blocks is not required here.

So, index blevel is one of the key indicators of performance of sql queries doing Index range scans.

Index depth is one of the prime factor involved in determining whether to rebuild an index. In most cases, you wouldn't need to rebuild any index. You may consider doing it when you have one off major delete/update done on that table.

You can more or less predict when would blevel of an index would go from one level to another level. If you have right hand index it is very easy to predict blevel, otherwise it becomes very hard. Here is a simple test case.

create table t(a number);
create index t_idx on t(a);
insert into t select rownum from all_objects where rownum <= 500;
analyze index t_idx compute statistics;
select blevel from user_indexes where index_name='T_IDX';

You would see around 540 rows to fit in one Index 8K blocksize. So, Index blevel would change when the number of index records reach power(540, blevel). Bear in mind that the number of indexed columns, their datatype, and the column size would dictate the number of records fit in one index block.

Blevel #Records
0 540
1 29K
2 150 million
3 8.5 billion

See the difference in number of records from blevel 2 to 3, that is the reason why you would hardly see index blevel going beyond 3.

If you have one off major deletes/updates done on your table, then consider rebuilding the affected indexes on that table. It all depends on the way you access and modify key values of that index.

posted by Saibabu Devabhaktuni @ 4:50 PM