Investigate violations of 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.

Edit the following advanced 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 foreign key 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 FKs are 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 (based on the amount of data that each column can hold).

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.

To determine if an index is large enough to be classified as wide, DPA uses the size of the data that each column can hold, based on its datatype. For this reason, DPA might flag an index as wide even though the size shown in the Existing Indexes section is below 200 bytes. Including columns that are intended to hold large amounts of data is generally not a good indexing practice.

For example, an Oracle index includes a column with the CLOB datatype, which can store up to 4 GB. DPA identifies that index as wide, even though the size listed in the Existing Indexes section is 189 bytes.

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)