Index Analysis

Edit online

Missing DBMS indexes is the most frequent cause of performance problems in an Archive or Delete Process. Archive allows you to analyze DBMS indexes for selected relationships and primary keys, allowing you to diagnose whether you need to create indexes.

If the status of an index is shown as Partial, or None, creating the necessary index may enhance processing performance.

Relationship Index Analysis

You can analyze DBMS indexes for relationships used with the Access Definition through the Specify Relationship Usage panel. Type SHOW INDEXES to display the Relationship Index Analysis panel.

Figure 1. Relationship Index Analysis

------------------------ Relationship Index Analysis --------------------------
Command ===>                                                  Scroll ===> PAGE 

                                                                  1 of 3 
AD: GRP.FOPDEMO.AD 

Parent Ix    Child Ix 
Stat/Needed  Stat/Needed  Relationship                   Parent Table 
-----------  -----------  ---------------------------->> ----------------------
************************************ TOP **************************************
NotAnlyzed   None       Y FOPDEMO.ORDERS.RCO             FOPDEMO.CUSTOMERS
Full       Y NotAnlyzed   FOPDEMO.DETAILS.RID            FOPDEMO.ITEMS
NotAnlyzed   Full       Y FOPDEMO.DETAILS.ROD            FOPDEMO.ORDERS
********************************** BOTTOM *************************************

The Relationship Index Analysis panel lists each selected relationship in the Access Definition, with an analysis of DBMS indexes for the corresponding parent and child tables.

Primary Key Index Analysis

Use Primary Key Index Analysis window to determine the tables in an Archive File that have an index on the primary key. The Primary Key Index Analysis pop-up window lists each table in the Archive File, with an analysis of DBMS indexes for the table.

From a Specify Parameters and Execute panel or the Delete Rows from Archive Process panel, use the SHOW INDEXES command to display the Index Analysis pop-up window listing the tables and the status of supporting indexes.

Figure 2. Index Analysis

 +----------------------- Index Analysis ----------------------+
 ¦                                                1 of 3       ¦
 ¦ Table Name                  Index Name         Index Status ¦
 ¦ --------------------------- ------------------ ------------ ¦
 ¦ ************************** TOP **************************** ¦
 ¦ FOPDEMO.ITEMS               XITEMPK            DBPK         ¦
 ¦ FOPDEMO.ORDERS              XORDRPK            Unique       ¦
 ¦ FOPDEMO.DETAILS                                Partial      ¦
 +-------------------------------------------------------------+

Status

The possible status of indexes for the Relationship Index Analysis and Index Analysis windows are:

DBPK: Index exactly matches the DBMS primary key definition for the table. (Index Analysis only)
Unique: A unique index is defined for the table; however, no primary key is defined. (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.
Note: On the Relationship Index Analysis window, 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 for the necessary columns exists.
NotAnalyzed: Index is not needed. (Relationship Index Analysis only)