Relationship Index Analysis

One or more missing DBMS indexes is the most frequent cause of performance problems in an Archive or Extract Process. 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 an Extract or Archive Process are analyzed.

Select Relationship Index Analysis from the Tools menu in the Access Definition Editor to display the Relationship Index Analysis dialog.

relationship index analysis display, showing 2 relationships

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.

Status

The status of DBMS indexes for the parent and child table columns in each relationship is displayed as follows:

None
Necessary indexes do not exist.
Partial
Necessary indexes for some relationship columns exist.
Full
Necessary indexes for all relationship columns exist.
Indeterminate
Optim attempted to create DBMS indexes. Click Refresh to analyze the new index.
Not Analyzed
No indexes are needed.

If the analysis determines that a DBMS index for a parent or a child table is needed to increase the efficiency of processing, the Needed check box is selected. If the check box is cleared, the index was not analyzed, or is not needed.

Note: You cannot create an index for an expression in a relationship that is not a column name (e.g., a concatenation, literal, or substring). If a relationship includes an expression that is not a column name, only the column name expressions that precede the non-column name expression can be indexed.

Shortcut Menu

Using the shortcut menu, you can create needed DBMS indexes, and create, edit or browse relationships. Right-click the grid on the Relationship Index Analysis dialog to display shortcut menu.

If the status of an index is shown as Partial, or None, select from the following shortcut menu options to create necessary indexes:

Create All Indexes
From the submenu, select the DB Alias for the database to be indexed, displaying the Review Index SQL dialog. Generated SQL statements for creating the indexes are displayed.
Create Index
Select Parent, Child, or Both from the submenu to display the Review Index SQL dialog. (If parent and child tables have different DB Aliases, Both is disabled.) Generated SQL statements for creating the DBMS index are displayed.
Open Relationship
Open the Relationship Editor to create or modify an Optim™ relationship or browse a database relationship. See Using the Editor for more information.
Note: If no expressions in a relationship can be indexed, a warning message is displayed.

In a generated SQL statement, the default name for a new index is in the form identifier.I_tablenamnnnnnnnn, where:

identifier
The identifier (Creator ID, Owner ID, or Schema Name) required by the DBMS to allow access to the database.
I_
Prefix for index name.
tablenam
First eight letters of the table name.
nnnnnnnn
Eight-digit, sequential number.

Review Index SQL

review index sql panel showing sql to create an index for column cust_id
New indexes are named using the identifier (Creator ID, Owner ID, or Schema Name), followed by the letter “I”, the first eight letters of the table name, and end with an eight-digit number, as follows:

identifier.I_tablenamnnnnnnnn

The Review Index SQL dialog also indicates if an expression in a relationship cannot be indexed.

Modify the name of the index, or other parts of the statements as necessary, then click Proceed to create the indexes.

The Browse Output dialog displays the results, after the SQL statements are executed.