Index Analysis

Missing DBMS 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 Archive to analyze DBMS 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 shortcut menu option.

Relationship Index Analysis

Use the Relationship Index Analysis dialog to analyze DBMS indexes for relationships used with the Access Definition and 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.

Note: Only indexes required for Archive (or an Extract) processing are analyzed.

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

Relationship Index Analysis dialog

The relationship name and the name of the parent table are shown on the right side of the grid. The analysis is presented on the left.

Primary Key Index Analysis

Use the Primary Key Index Analysis dialog to determine the tables in an Archive File that have an index on the primary key, and to create any needed indexes. The Primary Key Index Analysis dialog lists each table in the Source File, with an analysis of DBMS indexes for each table.

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

The name of the table(s) is shown on the left side of the grid, and the status is presented on the right.

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 may 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 may 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 may 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 referencing the database tables does not exist in the current Optim Directory. (Primary Key Index Analysis only)