Recommended Indexes

About Recommended Indexes

This section provides information about recommended indexes.

This task explains the procedure to work with recommended indexes:

  1. Login as cisdba and run edm.sh from $EDS_HOME/bin directory. For additional information see Using CES Database Manager.

  2. From the EDM main window, select a database for which the indexes are to be analyzed.

  3. Select Tools > Regular Index Management > Recommended Indexes.

    The Recommended Indexes window appears
    .

  4. Click Analyze to analyze all properties and generate a recommendation report and index creation scripts. A message prompting you to update the statistical information for the entire CISDBA schema appears. This is required to generate a recommendation report.

  5. Select the check box and click OK to update the statistics if your statistics are not up-to-date. After the statistics have been updated, the properties are analyzed, the index creation scripts are generated. A recommendation report is also generated. Index is not recommended or created for:

  • any class that has less than 500 objects.

  • any property that has the Is Unused in Search attribute marked Yes.

When all the conditions are met, the row selectivity algorithm is applied to the property and index is recommended based on the following condition:
Row selectivity for a property (RS) = (Number of distinct values for this property / Number of non-null values for this property) * 100

  •  For a regular property, If RS >= 70, create non-unique index

  • For a pointer property, If RS > = 50, create non-unique index

  • For a concatenated PK index
    The index member is organized by RS in descending order.

    • For a regular property
      The RS must be >=70 for all the members
      If RS > = 70 for a non-primary member of the PK index, a non-unique index will be created.

    • For a pointer property
      The RS must be > = 50 for all members
      If RS > = 50 for a non-primary member of the PK index, a non-unique index will be created

View Recommendation Report

After the properties have been analyzed, click View Report tab to view the recommendation report.

The View Report window appears.

 

Name Description

Table Name

The DB Name of the class for which the Row Selectivity Report has been generated.

Population

Number of rows in the class.

Column Name

The DB Name of the property.

Row Selectivity

The row selectivity of this property.

Create Index

Indicates whether index is recommended for this property.

Create indexes

After the properties have been analyzed, click Create Indexes tab to view and execute the index creation scripts.

The Create Indexes window appears.

Index creation scripts are generated for Metamodel, Predefined, and User classes. These are categorized under the three tabs.

You can perform these tasks:

  • Edit one index statement at a time. To do so, click the row header of the Create Index statement and click Edit.

  • Create one or more of these indexes. To do so, click the row headers of the corresponding Create Index statements and click Execute.

Note: These index creation scripts are stored in $EDS_HOME/log directory.