SPD Server Component File Types and Sizes

SPD Server uses four types of component files. Component files are physical file entities that SPD Server uses to track table and index metadata. When component files are combined, they form a logical structure that SPD Server understands and interprets as a single table. The following table lists the relative sizes of the four types of SPD Server component files.

File Type	File Extension	Relative Size	Number of Component Files
Table metadata	.mdf	Very small	1
Table data	.dpf	Large	1 to many
Segmented index	.idx	Medium to large	0 or more
Global index	.hbx	Medium to large	0 or more

At a minimum, an SPD Server table consists of two component files, the metadata .mdf file and the data .dpf file. The size of the data file component depends on two factors: the size of a table column and the number of columns. The data .dpf component file can be many gigabytes in size. SPD Server is not constrained by an operating system file system size limit (such as the 2-gigabyte limit on file size that some UNIX systems impose).

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 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 requires a larger segment list. 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.