As an index grows, Oracle allocates new blocks. If a new index entry is added beyond the last
previous entry, the new entry will be added to the last block in the index. If the new entry causes
Oracle to exceed the space available in that block, the entry will be moved to a new block. There
is very little performance impact from this block allocation.
If the inserted rows are not ordered, new index entries will be written to existing index node
blocks. If there is no more room in the block where the new value is added, and the block is not
the last block in the index, the block??™s entries will be split in two. Half the index entries will be
left in the original block, and half will be moved to a new block. As a result, the performance
suffers during loads (because of the additional space management activity) and during queries
(because the index contains more unused space, requiring more blocks to be read for the same
number of entries read).
NOTE
There is a significant drop in load performance when an index
increases its number of internal levels. To see the number of levels,
analyze an index and then select its B level column value from DBA_
INDEXES.
Because of the way Oracle manages its indexes internally, load rates will be affected each
time a new index is added (because it is unlikely that inserted rows will be sorted correctly for
Chapter 8: Database Tuning 249
multiple columns). From a load rate perspective, favor fewer multicolumn indexes over multiple
single-column indexes.
Pages:
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418