sizing indexes, does anyone have an accurate script or algorithm?

Does anyone have an index sizing script for Oracle8 (or 7) which they are happy with, and willing to share? I really find it hard to believe that there isn't an abundance of these scripts. Wouldn't it be logical for Oracle to distribute sizing tools with OEM?

I've spent quite some time going thru the Oracle8 documentation and various other resources trying to accurately size or estimate the space requirements for primary keys and unique indexes. The estimates are generally well below the actual number of consumed blocks shown in dba_segments.

To test my scripts I've tended to create simple tables, with 500 or 1000 rows. Then I create a unique index, with an initial and next extent of one block. Unfortunately, the results aren't very inspiring. For example, if my estimate says I will need 4 blocks for the index, I have found that it will actually consume 7 blocks. Yesterday I created an index that was estimated to need 8 blocks, and actually consumed 18.

The only immediate way I can see to improve things (immediately) is to increase the overhead for branches from 10% to more like 80%, but that would be ridiculous wouldn't it?

I've repeatedly asked support for either a script or a better algorithm, and got no where. (Although, one support person did tell me that I'd be surprised to know how many people keep asking them about this.) I'm still optimistic that they have the script I want, but that for some reason I haven't given them exactly the right keyword to find it in their database.

So, can anyone provide me with a script (or TAR number to get one), that is satisfactorily accurate? And how about sizing tables?

Thanks in advance for any help,

Nick Borrell
DBA/Developer
New Zealand Police
+64 237 2811 x5459 Received on Thu Mar 26 1998 - 00:00:00 CST