Re: rebuild index?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 15 Jun 1999 20:21:30 +0100
Message-ID: <929474797.12105.0.nnrp-02.9e984b29@news.demon.co.uk>

Sorry, talking rubbish there. The note below describes the avg_leaf_blocks_per_key from the XXX_INDEXES view.

The block_gets_per_access column in
INDEX_STATS also relates to the number
of rows with a given value, but is an estimate of the number of logical I/Os required to get a given row from the table using that index, and I think it tends to work out as
(average rows per key / 2) plus a bit

So it still is not an indicator of whether or not the index should be rebuilt.

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Jonathan Lewis wrote in message
<929471723.28362.0.nnrp-07.9e984b29_at_news.demon.co.uk>...
>The Blk_gets_per_access is not necessarily
>a good indicator - for a unique index bgpa will
>be height of index + 1, for a non-unique key
>bgpa will have an extra factor which relates
>to number of rows per key value and number
>of (expected) index entries per index block.
>
>So an index with about 100 entries per key
>value, but only expected 20 index entries per block
>(i.e. key size is about 100 bytes) would
>have a bgpa of around 5 + 3 if the index
>were very well packed, and 10 + 3 if the
>index was running at 50% efficiency.
Received on Tue Jun 15 1999 - 14:21:30 CDT