Help

Observation: Many Table Joins

The number of table joins exceeds the limit for a single statement. For DB2, creating the result set from many tables can be one of its most processing-intensive tasks. If you denormalize the tables and minimize the joins, you can potentially improve performance by reducing:

  • The number of tables referenced.

  • The frequency and complexity of joins.

  • The number of foreign key columns to update and, assuming indexes are defined for the foreign keys, the indexes to maintain.

However, the potential benefits of denormalizing depend upon how frequently the tables are accessed, how they're processed, and their size. Consider some of the following criteria:

  • The tables are referenced concurrently using different columns.

  • The tables are joined on a regular basis.

  • A subset of columns is selected on a regular basis.

  • Critical queries are issued against multiple tables in an online environment.

  • The tables are defined with large, primary keys that consume a large amount of disk space when defined in dependent tables as foreign keys.