Index Space Requirements

Estimating the amount of space occupied by an index is difficult. The XDB Server uses B-tree indexes. The values in an indexed field and a pointer to the record containing it are stored in the *.idx file for the index; pointers to each duplicate instance of the value are stored in the *.ptr file for the index. The *.ptr file will contain 0 bytes if the index was created using the UNIQUE option.

Before you start you must have an estimate of the number of records in the table and the number of distinct values in the index.

Formulas for estimating space requirements for indexes are described in this topic. The formulas use the following variables:

I Estimated index storage required (bytes)
V Number of distinct values in index (estimate)
N Number of records in the table (estimate)
S Storage requirement for index value

(Depends on index type)

Dj Data storage requirement for field j

(Depends on data type. See the following table.)

Oj Overhead for field j

(Depends on data type. See the following table.)

f Number of fields in index

The bytes of storage required for an index is calculated as follows:


Formula for calculating the bytes of storage required for an index

Note that if the index is created using the UNIQUE option, the number of records is equal to the number of distinct values, and the equation for calculating index storage requirements is reduced to:


The equation for calculating index storage requirements

In either case, S, the storage requirement for indexed values, depends on the type of index.

Regular Index

To compute the storage requirement S for a single field index, use this formula:


Formula to compute the storage requirement for a single field index

Compound Index

To compute the storage requirement S for a compound index on f fields, use this formula:


Formula to compute the storage requirement for a compound index