The SPD
Server index uses two index component files: the
.hbx file and the
.idx file. The
.hbx file maintains a global view of the index, and
contains an entry for each key that exists in the index. The
.idx file maintains a segmented view of the index that
includes a list of all of the segments that each key is a member of.
A bitmap is used to determine the per-segment observations for each
key.
The size
of the
.hbx file depends on the cardinality
of the index keys. The higher the cardinality of the index keys, the
larger the
.hbx file. The size of the
.idx file is much more difficult to determine because
it depends on how the data for the index keys is distributed across
segments. An index key that is in many segments will require a larger
segment list, and larger segment lists require a larger number of
per-segment bitmaps, as compared to an index key that is found only
in a small number of segments.
The best
case scenario for creating an optimally sized
.idx file occurs when the table is sorted by the indexed columns, in
order to minimize the number of segments that the key is in. The worst
case scenario for creating an optimally sized
.idx file occurs when the index keys are in a large number of segments,
with a low cardinality of rows for each segment.