Considerations when choosing table spaces for your tables

When determining how to map tables to tables spaces, you should consider the distribution of your tables, the amount and type of data in the table, and administrative issues.

The distribution of your tables

At a minimum, you should ensure that the table space you choose is in a database partition group with the distribution you want.

The amount of data in the table

If you plan to store many small tables in a table space, consider using SMS for that table space. The DMS advantages with I/O and space management efficiency are not as important with small tables. The SMS advantages, and only when needed, are more attractive with smaller tables. If one of your tables is larger, or you need faster access to the data in the tables, a DMS table space with a small extent size should be considered.

You might want to use a separate table space for each very large table, and group all small tables together in a single table space. This separation also allows you to select an appropriate extent size based on the table space usage.

Important: User table spaces that use System Managed Space (SMS) are deprecated and might be removed in a future release. Use Database Managed Spaces (DMS) or Automatic Storage table spaces (AMS) instead.

The type of data in the table

You might, for example, have tables containing historical data that is used infrequently; the end-user might be willing to accept a longer response time for queries executed against this data. In this situation, you could use a different table space for the historical tables, and assign this table space to less expensive physical devices that have slower access rates.

Alternatively, you might be able to identify some essential tables for which the data has to be readily available and for which you require fast response time. You might want to put these tables into a table space assigned to a fast physical device that can help support these important data requirements.

Using DMS table spaces, you can also distribute your table data across four different table spaces: one for index data; one for large object (LOB) and long field (LF) data; one for regular table data, and one for XML data. This allows you to choose the table space characteristics and the physical devices supporting those table spaces to best suit the data. For example, you could put your index data on the fastest devices you have available, and as a result, obtain significant performance improvements. If you split a table across DMS table spaces, you should consider backing up and restoring those table spaces together if rollforward recovery is enabled. SMS table spaces do not support this type of data distribution across table spaces.

Administrative issues

Some administrative functions can be performed at the table space level instead of the database or table level. For example, taking a backup of a table space instead of a database can help you make better use of your time and resources. It allows you to frequently back up table spaces with large volumes of changes, while only occasionally backing up tables spaces with very low volumes of changes.

You can restore a database or a table space. If unrelated tables do not share table spaces, you have the option to restore a smaller portion of your database and reduce costs.

A good approach is to group related tables in a set of table spaces. These tables could be related through referential constraints, or through other defined business constraints.

If you need to drop and redefine a particular table often, you might want to define the table in its own table space, because it is more efficient to drop a DMS table space than it is to drop a table.