Re: indexes reserve too much extents

Frederic Hoornaert wrote:
> Hello,
>

[snip]

As others have said, 30 indexes for a table is getting a bit silly (sounds like SAP to me).

> When I take a close look at the data of INDEX_STATS after a VALIDATE
> INDEX <index_name>, i see that
> 16 indices have each 1 extent of 64 Mb

Which sort of suggests they were built into the wrong tablespace. Locally Managed Tablespace will not sweat with a couple of hundred extents, so you might be better off rebuilding these indexes into the 2M tablespace (or even the 64K tablespace -why you picked those extent sizes is a puzzle. Autoallocated tablespace uses 64K, 1M, 8M, 64M and 256M extents. If it's good enough for Oracle, I'd suggest you should have done likewise).

> 1 index has 5 extents = ( 64Mb * 5 ) = 320 Mb
> 13 indices have each 8 extents = (64 Mb * 8) = 512 Mb.

Again, all very low numbers of extents, and all could therefore be rebuilt into the smaller extent-sized tablespace without drama. With smaller 'granularity' of extent sizes, you'll probably save some space.

> For the indices that all use 8 extents, something seems to be wrong
> because for these indices I see that they all use between 50 and 150 Mb
> (value of index_stats.used_space)
>
> Largest index in tablespace :
> extent size : 64 Mb / (8192 byte / block) => 8192 blocks
> used blocks : 65536 / (8192 blocks / extent) => 8 extents
> btree_space : 177.058.656 byte
> 177.058.656 / (8192*8192) = 2,6
>
> Why are these indexes using 8 extents where is space enough in 3 extents ?
> For this table, I have 8.388.608.000 bytes assigned where only
> 1.635.157.943 bytes are needed.
>
> This is the situation after the creation of an empty database, and data
> import with SQL Loader. No manual manipulation had been done yet.

For the particular weirdness of multi-extent allocation when only a few would have done, I might suspect that parallelism has been involved somewhere along the line. If a serial rebuild reclaims the space, that might answer the question.

But that's really not your real problem. Too many indexes, and tablespaces with slightly odd uniform extent sizes are.

Regards
HJR Received on Mon May 03 2004 - 17:40:54 CDT