[EMAIL PROTECTED] wrote: > is there someone who can tell me how much storage an index consumes? > - index on an INTEGER column > - index on a VARCHAR column > - index on a composite unique key (e.g. VARCHAR + INTEGER or > INTEGER + INTEGER) > > Is there any estimation formula available to 'calculate' the > storage consumption of indexes?
Yes and no, but it is not that easy: Each indexvalue is stored once together with 12 bytes of overhead. The (n-1) first columns of a composite key (no matter if primary / secondary / unique) are always fixed length, the n-th column is always of variable length. For the length of a single column see http://www.sapdb.org/htmhelp/e0/ed9036dfe4b903e10000009b38f889/frameset.htm Together with the indexvalue all primary keys of those rows having this indexvalue are stored. If the lengths of all primary keys of one indexvalue (together with this indexvalue) do not exceed around 6000 bytes, that is all for this indexvalue. If they exceed this summed-up length, the primary keys of this indexvalue are stored in a separate subtree --> each primary key has an overhead of 8 bytes + the space needed for the separators in the non-leaf-levels of this subtree to form a B*-tree. And of course + the separators needed to form the non-leaf-levels of the main-tree, in which the indexvalue is the key on the leaf level of this main tree. You see: it is hard, but possible. Elke SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
