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.
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
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.