Help

Observation: Many Tables in Tablespace

The number of tables in the tablespace exceeds the limit for a single, unsegmented (simple) tablespace. You might include multiple tables in a single simple tablespace to improve the performance of referential constraints and some joins. However, a simple tablespace can contain one or more tables and, consequently, a single page can contain rows from all tables defined to the tablespace. This can adversely affect the following:

  • Concurrent access of multiple, unrelated tables: locking occurs by page, not table, so a lock taken for one of the tables containing rows on a page could impact concurrent requests for rows of the other tables on the same page.

  • Tablespace scans: because rows for a table might appear on any page, all pages within the tablespace, including pages that might not contain rows for the target table, are scanned.

  • Mass delete of rows for a specific table: for simple tablespaces, all rows for a target table are first physically read, then deleted. Generally, this results in higher rates of log activity than with segmented tablespaces.

  • Space management: the overhead required to maintain page maps within simple tablespaces can be larger than the actual data. Therefore, simple tablespaces might not be the most appropriate tablespace type for small tables.

Segmented tablespaces offer the following advantages over simple tablespaces:

  • Any single page contains rows for only one table: tablespace scans search only the pages containing rows of the target table. Also, locking a page for a specific table does not negatively impact access to rows of other tables located in the same tablespace.

  • DB2 manages free space more efficiently for segmented tablespaces: this results in less overhead for inserts and variable-length row updates.

  • DB2 reclaims space immediately for dropped tables: simple tablespaces require a subsequent reorganization to reclaim the space.

  • Mass delete processing is more efficient: unlike simple tablespaces for which DB2 actually deletes the data, DB2 need only update the space map for segmented tablespaces.