Re: Index extents keep increasing but data remains the same

ag831_at_FreeNet.Carleton.CA (Denis Langlais) writes:
:
: I have 2 indexes on a table. One of those indexes
: has extents which keep increase until they fill up
: the tablespace they are in. The table fills up
: every day with stats and then most rows are
: deleted. (summarized in another table)
:
: So even though the rows are kept small the extents
: of 1 index keep increasing. This index is based
: on a field in the table which keeps incrementing
: so some of the old rows in the table have old numbers.
: Maybe the btree is unbalanced?
:
: Oracle says the only way to solve this problem is
: to drop the indexes and recreate them before the
: tablespace fills up.
:
: This is a pain however? Any better suggestions?

You say you are regularily deleting and re-entering data in the table. However, are you reusing key values(or values for whatever the indexed columns are)?

If you have an index on a primary key, and you are constantly deleting and re-entering records, but are NOT re-using your primary key values(ie, your primary key is constantly increasing, say by a sequence), then the space freed up in the index segment by the deletion of the rows will not necessarily be re-used by the new rows. This is not a bug, just simply due to the mechanisms of balancing a B-Tree structure.

--
regards,
Carl
+-----------------------------------------------------------------------------+
Carl Gohringer, 
Oracle UK, The Oracle Centre, The Ring, Bracknell, Berkshire, England, RG121BW
Internet   : cgohring_at_uk.oracle.com    
+-----------------------------------------------------------------------------+