Creating Indexes

The Create Indexes functionality in CES Database Administration allows you to do the following:

Analyzing All Properties and Generating Reports/Scripts

You can analyze all properties and generate reports and scripts. The topics covered in this section are:

This task explains how to analyze all properties and generate reports and scripts:
  1. From the navigation pad, select Tools > Index Management > Create Indexes > Recommendations to Create. The Recommendations to Create page appears.
     

In the Recommendations to Create page, you can do the following:

  • Analyze all properties and generate index creation scripts.
    • Click Row Selectivity Analysis tab to analyze all properties and generate a recommendation report and index creation scripts.
    • To generate bitmap indexes, select Generate Bitmap Index and click Analyze.

      Maintaining bitmap indexes is expensive. Bitmap indexes are not
      recommended in an OLTP environment where constant index update is required. Bitmap indexes are created only when the Column Index Type = 6 for properties, which must meet the Row Selectivity (<=3), object count(>499) conditions and Is Unused in Search Attribute is set to FALSE.

      Index is not recommended or created if:
    • Any class has less than 500 objects.
    • Any property has the Is Unused in Search Attribute and is marked YES.

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

Viewing Recommendation Report

After analyzing the properties, you can view the recommendation report.

This task explains how to view the recommendation report.
  1. The Recommendations to Create page, click the View Report Report tab to view the recommendation. The Recommendations to Create - View Report page appears.

The details displayed in the Recommendations to Create - View Report page is described in the following table:

Options Details
Table Name The DB name of the class that has been analyzed.
Population Number of objects in the table.
Column Name The DB name of the property in the class.
Row Sensitivity The row selectivity of the column.
Create Index Indicates whether index has been created for the property and the type of index - User or Bitmap.

Creating Indexes

After analyzing the properties, and viewing the report, you can create indexes.

This task explains how to create indexes.
  1. The Recommendations to Create page, click the Create Indexes tab to view and execute the index creation script. the recommendation. The Recommendations to Create - Create Indexes page appears.

Index creation scripts are generated for Metamodel, Predefined, and User classes. These are categorized under the three tabs. You can do the following:

  • Edit one or more of the scripts. To do so, select the script and click Edit.

  • Create one or more of these scripts. To do so, select the script and click Create. The message All selected index(es) successfully created appears.

Note: These files are stored in $EDS_HOME/log directory.

Creating an Index

The Creating an Index allows you to create an index for a specific class in CES Administration Database. 

This task explains how to create an index for a specific class:
  1. From the navigation pad, select Tools > Index Management > Create Index for a given class. The Create Index For a Given Class page appears.
     

The Create Index For a Given Class page displays list of CES classes either by their Class Labels or Class Names. You can search for a given class, either by Class Label or Class Name, by specifying the value in the Search field, or by selecting the value from the list. Click Get Properties to display list of properties associated with the selected class. A list of associated properties appears, as explained below:
Properties Details
Property Label The name of the property for which an index is to be created.
Property DB Name The DB name of the property for which an index is to be created.
Data Type The datatype of the property.
Length The length of the property.
Is Indexed Indicates if the property is indexed.

  1. To create an index on a given property whose Is Indexed  status is NO for a
    selected class, click Create. The index creation dialog box appears.

  2. Type the Index Name and click OK  to create the index. After the index is created, the Is Indexed field is updated to YES.

Function Based Index

A Function Based Index (FBI) is an index or an expression of a column. When you search a column where a column function is required, such as UPPER (u_part_name) = XYZ123, Oracle does not use any regular indexes. However, if you have the right function-based index defined, Oracle uses this and the search is much faster. Function Based Indexes can be used for text properties whose property length is short and which have indexes built for search performance. The topics covered in this section are:

Creating/Dropping Function Based Index

You can create/Drop a function based index in the CES Administration Base:

This task explains how to create/drop a function based index:
  1. From the navigation pad, select Tools > Index Management > Function Based Index. The Function Based Index page appears.

    The Function-Based Index page displays list of CES classes either by their Class Labels or Class Names. You can search for a given class, either by Class Label or Class Name, by specifying the value in the Search field, or by selecting value from the list.

    Click Get Properties to display list of eligible properties associated with the selected class on which FBI can be created or from which FBI can be dropped.

    After you select the class name from the list, a list of associated properties appears as explained in this table below:
    Properties Details
    Property Label The name of the property for which an index is to be created.
    Property DB Name The DB name of the property for which an index is to be created.
    Data Type The datatype of the property.
    Length The length of the property.
    Is Indexed Indicates if the property is indexed.
    Function Name This is the name of the case style of the property. This can be:
    • UPPER. If the text property is marked with UPPER case, the property data will be stored in upper case in the database.
    • LOWER. If the text property is marked with LOWER case, the property data will be stored in lower case in the database.

Creating Function Based Index

When you select the class for the FBI needs to be defined, the eligible properties are displayed.

This task explains how to create a function based index:
  1. Select the property for which an FBI needs to be created.

  2. Specify the Function Name (case style) to be used for the property. This can be:

    UPPER. If the case style is UPPER, the user search value is converted to upper case and is matched with the database property data.

    LOWER. If you case style is LOWER, the user search value is converted to
    lower case and is matched with the database property data.

  3. Click Create. The FBI is created for this property and the Is Indexed field is updated to Yes and the case style specified for the property appears in the Function Name field.

Dropping Function Based Index

When you select the class for the FBI needs to be defined, the eligible properties are displayed.

This task explains how to drop a function based index:
  1. Select the property for which the FBI needs to be dropped.

  2. Click Drop. The FBI is dropped for this property and the Is Indexed field is updated to No, and the case style is removed for the selected property.