Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> indexes reserve too much extents
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
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.
-- Greetz, Frederic Hoornaert (Plz remove .nospam from my email address when replying to this message)Received on Mon May 03 2004 - 09:47:50 CDT