[EMAIL PROTECTED] wrote: > Your objection seems true enough! > > But how about to make up a ratio of the index storage to the > data storage used for the indexed fields > (ratio=average((storage used for index pages)/(length of the > indexed fields)))? > > Such a ratio is independent of the total length of the whole > records but depends on the length of the indexed fields. So > one gets an estimation formula to seize the storage usage by > the indexes. This approach has indeed the disadvantage that one has to > estimate such a ratio for each type of indexed fields (data > type of the indexed fields, composite keys). > > kind regards > Frank Schimmelpfennig >
If you just want to calculate the space needed for another index on a table you have one index on, then your ratio mentioned above may be ok. But if you think of different tables, you have another problem: You forgot the different kinds of primary keys. As I told you, index + all primary keys are stored. therefore, if one table has a small primary key of (let's say) an integer and the other table has a key, of 3 names, each with 40 bytes, one cannot compare space needed for an index in one table with an index of the other, even if the indexed column may have the same datatype and length. Elke SAP Labs Berlin > > [EMAIL PROTECTED] > > > Hi Elke, > > > > meanwhile I did some experiments with a simple table (three > > columns: int_col1 integer ,int_col2 integer ,char_col > > varchar(50), primary key: int_col, unique key: int_col2): > > Each time I inserted 128 rows into this table I determined > > the total storage > > consumption by the data and number of pages for the unique > > key index (as Daniel mentioned). Then I made up the ratio of > > the index storage to the data storage for each insert. > > > > After 30 inserts the average of this ratio is about 0.4 (40%). > > > > Maybe this average ratio can make up such a supplementary > > factor for indexes to be added on the storage consumption for > > the data. Perhaps there is someone with some SAP DB > > experience who can confirm the value of 40% for such a index > > factor during database > > sizing. > > > Mmhm, I do not think, that this ratio can be taken for all indexes. > We know of customers having records of about 7500 bytes in length. > If THEY had this index (primary and secondary key are integer) > THEIR ratio would be much smaller. > If YOUR char_col was NOT filled with some data, YOUR ratio > would be even > higher. > If ALL char_cols were filled to the full (or you had two > char_cols of 25 > bytes each) > your ratio would be smaller, too. > > Elke > SAP Labs Berlin > > > > _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
