Considering Physical Fragmentation Factors

When you fragment a table, the physical placement issues that pertain to tables apply to individual table fragments. For details, see Table Performance Considerations. Because each fragment resides in its own dbspace on a disk, you must address these issues separately for the fragments on each disk.

Fragmented and nonfragmented tables differ in the following ways:

• For fragmented tables, each fragment is placed in a separate, designated dbspace or multiple partitions of the table are created within a single dbspace.

  For nonfragmented tables, the table can be placed in the default dbspace of the current database.

  Regardless of whether the table is fragmented or not, it is recommended that you create a single chunk on each disk for each dbspace.

• Extent sizes for a fragmented table are usually smaller than the extent sizes for an equivalent nonfragmented table because fragments do not grow in increments as large as the entire table. For more information on how to estimate the space to allocate, see Estimating Table Size.
• In a fragmented table, the row pointer is not a unique unchanging pointer to the row on a disk. The database server uses the combination of fragment ID and row pointer internally, inside an index, to point to the row. These two fields are unique but can change over the life of the row. An application cannot access the fragment ID; therefore, it is recommended that you use primary keys to access a specific row in a fragmented table. For more information, see the IBM Informix: Database Design and Implementation Guide.
• An attached index or an index on a nonfragmented table uses 4 bytes for the row pointer. A detached index uses 8 bytes of disk space per key value for the fragment ID and row pointer combination. For more information on how to estimate space for an index, see Estimating Index Pages. For more information on attached indexes and detached indexes, see Fragmenting Indexes.

Decision-support queries usually create and access large temporary files; placement of temporary dbspaces is a critical factor for performance. For more information about placement of temporary files, see Spreading Temporary Tables and Sort Files Across Multiple Disks.