Re: Growth of Index

srtope_at_aol.com (SRTOPE) writes:

:tables that continue to grow and grow. These indexes are generally yes/no
:columns: a number of rows get created with an 'N' and then are updated to
:a 'Y' when the record is further processed, hence a huge number of 'Y'
:records are created in the index. Is this lopsided updating the reason for
:my index growth?

Updating a value for an indexed table, especially when the field has so few unique values, is very likely to cause index growth, assuming I remember index tree structure correctly. Are you doing much more updating of rows than inserting of new rows?

:Rebuilding the index helps for awhile but is it reasonable for a one byte
:index on a 1 million row table to be 20M in size? I'm still struggling
:with the oracle 7 admin manual's index size calculation formula.

Well, in addition to the data field (only ONE one-character field in the index, that's almost not worth having an index, IMO) you have the ROWID, which I believe is six bytes long, plus some overhead. That might make it seem less wasteful of space.

I've said it before, but if 20 megabytes seems like a lot of disk space to you, maybe Oracle isn't for you.

---
Michael Nolan, Sysop for the DBMS RoundTable on GEnie
nolan_at_tssi.com, dbms_at_genie.com, nolan_at_inetnebr.com
(posted from nolan_at_helios.unl.edu)