Documentation forDatabase Performance Analyzer

Table tuning best practices

When DPA generates a table tuning advisor, it evaluates the table and its indexes against a set of best practices. Any violations are listed in the Current Table Information section.

If violations are found, consider the following recommendations.

Use the following advanced configuration options to change the default values that DPA uses to check for best practices:

  • To prevent DPA from checking for compliance to a best practice, change the corresponding BEST_PRACTICES_<practiceName> option to false.
  • Use BEST_PRACTICES_WIDE_INDEX_SIZE to change the minimum size of a wide index.
  • Use BEST_PRACTICES_NUMBER_OF_COLUMNS_IN_WIDE_INDEX to change the minimum number of columns in a wide index.
  • Use BEST_PRACTICES_NUMBER_OF_OVERLAPPING_COLUMNS to change the minimum number of leading edge columns that indexes must share to be classified as overlapping.

Foreign key (FK) is not indexed

A FK in one table (the child table) refers to the primary key of another table (the parent table). Indexing each FK can improve the performance of queries that join the two tables. In addition, when a FK is not indexed, the database must perform a full table scan of the child table whenever a row is deleted or the primary key value is updated in the parent table.

Overlapping indexes found: At least two indexes have the same leading edge columns

Overlapping indexes have the same leading edge column (the first column defined). Because every index has a maintenance cost and consumes disk space, identifying and removing unneeded indexes can improve performance. Examine the overlapping indexes to determine if any can be removed. For example: 

  • If two indexes include the same columns in the same order but one includes additional columns, the smaller index is redundant and can be removed.
  • If two indexes include the same columns but each has one additional column, modify one index to include all columns and remove the other index.

Wide index found: Index contains five or more columns or is more than 200 bytes

A wide index meets at least one of the following criteria:

  • The index includes five or more columns.
  • The index is more than 200 bytes.

Large indexes require more storage and increase the cost of index maintenance. If the index includes five or more columns because it is a covering index for multiple queries, the performance improvement might offset the additional overhead. However, if the index is not a covering index, the cost of maintaining the index could offset any performance improvement that the index provides. In this case, consider removing trailing edge columns.

Table has no defined indexes

The table is being queried, but no indexes exist. If the table is very small, an index might not be more efficient than a full table scan. For larger tables, consider adding an index.

Table does not have a primary key (PK)

A PK provides a way to uniquely identify a record and is necessary to ensure data integrity. A PK is also required to join the table. If no column or combination of columns provides a unique value, you can add an artificial PK such as an ID column.