Balancing Index Extents?

Hi all,

Our database has several hundred customer tables. These tables are recreated for each month from current to last 6 months. All tables are created with the same index keys. The index storage specifications have been carefully computed to minimize dynamic extent creation and also to more or less synchronize index extents additions with the underlying table extents.
However, at the end of each month when the tables are now filled with varying number of rows from thousands to millions, some of the index extents have grown way over their table extents. Smaller tables tend to have a balanced table and index extents. As the tables grow bigger, something causes the index to use more extents. Repeated adjustments to index specs didn't help much. On further investigation, using analyze index, i noticed that the LF_BLK_LEN of the INDEX_STATS table were different for some of the indices. In fact, it was smaller for tables with the high index extents. LF_BLK_LEN is defined as "usable space in a leaf block." Why would it be different for indices of the same structure? Is it causing the problem? Is there a different index space calculation from the one defined in the manual?

Any help is appreciated.

Thanks,
Arnel Pajarillo Received on Wed Oct 30 1996 - 00:00:00 CET