Table-location guidelines

This topic lists some strategies for optimizing the disk layout, given certain characteristics about the tables in a database.

You can implement many of these strategies with a higher degree of control using table fragmentation:
  • Isolate high-use tables on a separate disk.

    To isolate a high-use table on its own disk device, assign the device to a chunk, and assign the same chunk to a dbspace. Finally, place the frequently used table in the dbspace just created using the IN dbspace option of CREATE TABLE.

    To display the level of I/O operations against each chunk, run the onstat -g iof option.

  • Fragment high-use tables over multiple disks.
  • Group related tables in a dbspace.

    If a device that contains a dbspace fails, all tables in that dbspace are inaccessible. However, tables in other dbspaces remain accessible. Although you must perform a cold restore if a dbspace that contains critical information fails, you must only perform a warm restore if a noncritical dbspace fails.

  • Place high-use tables on the middle partition of a disk.
  • Optimize table extent sizes.

For more information, see the chapter on table performance considerations in your IBM® Informix® Performance Guide. For information about onstat options, see the IBM Informix Administrator's Reference.