Index Analysis

Missing database indexes are the most frequent cause of performance problems in an archive, extract, or delete process. Use the index analysis tools that are provided with your Optim™ solution to analyze indexes for relationships and primary keys and to create any needed indexes.

If the status of an index is shown as Partial, or None, you can create any needed indexes from a menu option.

Relationship Index Analysis

For archive or extract processing, use the Relationship Index Analysis dialog to analyze database indexes for relationships used with the access definition. You can also use this dialog to create any needed indexes. The Relationship Index Analysis dialog lists each selected relationship in the access definition, with an analysis of DBMS indexes for the corresponding parent and child tables.

Click Tools > Relationship Index Analysis in the Access Definition Editor to open the Relationship Index Analysis dialog.

Relationship Index Analysis dialog

Primary Key Index Analysis

The Primary Key Index Analysis dialog lists each table in the source file, with an analysis of DBMS indexes for each table. Use the Primary Key Index Analysis dialog to identify tables in an archive or extract file that have an index on the primary key. You can also use the Primary Key Index Analysis dialog to create any needed indexes.

Right-click a table name in the Table Access Strategy dialog and click Analyze Primary Key Index for this table or Analyze Primary Key Index for all tables to open the Primary Key Index Analysis dialog.

Primary Key Index Analysis dialog

Status

The possible status of the indexes for the Relationship Index Analysis and Primary Key Index Analysis dialogs are:

DBPK
An index on the database primary key is defined for the table and is used to access the table. (Primary Key Index Analysis only)
Unique
A unique index on an Optim primary key is defined for the table. The primary key columns can be indexed in any order. (Primary Key Index Analysis only)
Full
An index on an Optim primary key is defined for the table. The index includes all primary key columns at the beginning of the index, in any order, but can include additional columns.

On the Relationship Index Analysis dialog, the Full status describes any DBPK, Unique, or Full index on the primary key.

Partial
An index on an Optim primary key is defined for the table. The index includes at least one primary key column at the beginning of the index, but can include additional columns.
None
No index exists with the necessary columns.
Indeterminate
Archive attempted to create DBMS indexes. Click Refresh to analyze the new data.
Not Analyzed
No indexes are needed. (Relationship Index Analysis only)
No PK
No primary key is defined for the table. (Primary Key Index Analysis only)
Table Not Found
The DB alias that references the database tables does not exist in the current Optim directory. (Primary Key Index Analysis only)


Feedback