Re: indexes reserve too much extents

With the indexes enabled, SQL Loader builds indexes while imporing data, each index leaflet occupies a data block, that is why the usage of index space swell.
If you turn off the index in SQL Loader by set indexes=n, then rebuild index afterwards, you will get much smaller index space.

John

On Mon, 3 May 2004 16:47:50 +0200, Frederic Hoornaert <Frederic.Hoornaert_at_transics.nospam.com> wrote:

> Hello,
>
> I'm having some questions about the space oracle has allocated to my
> indexes :
>
> Our OLTP application uses a database with the following specifications
> DATA tablespaces :
> data_small : locally managed unfiform size 128k
> data_medium : locally managed unfiform size 4M
> data_large : locally managed unfiform size 128M
>
> index tablespaces :
> index_small : locally managed unfiform size 64k
> index_medium : locally managed unfiform size 2M
> index_large : locally managed unfiform size 64M
>
> minimum extents of all tablespaces = 1
> DB_BLOCK_SIZE = 8192
>
> index_small contains the indexes on the tables in data_small
> index_medium contains the indexes on the tables in data_medium
> index_large contains the indexes on the tables in data_large
>
> I have been analyzing the sizes of our tablespaces, and i'm having some
> difficulties with the size of the indexes.
> data_large = 6 Gb
> index_large = 17.8 Gb
>
> Because 17.8 Gb for only 6 Gb of data seems to be too much, I have
> analyzed all tables and all indexes.
> One table with 5.979.487 rows has a size of 163.840 blocks in 10 extents
> (1 extent = 128M)
> assigned size = 1,25 Gb
> This table has 30 indices which use 1.024.000 blocks => 7,8 Gb
>
> 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
> 1 index has 5 extents = ( 64Mb * 5 ) = 320 Mb
> 13 indices have each 8 extents = (64 Mb * 8) = 512 Mb.
>
> 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.
>

-- 
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/