Dropping Indexes

This section describes about the working with Drop Indexes. The topics covered are:

Recommendations to Drop

You can make recommendations to drop the indexes by specifying values in CES Database Administration.
This task explains how to work with Recommendations to Drop:
  1. From the navigation pad, select Tools > Index Management > Drop Indexes > Recommendations to Drop. The Recommendations to Drop-Generate page appears.
     

From Recommendations to Drop-Generate page, you can:

  • Generate Non-Selective indexes report and the drop index script.

Based on the Block Selectivity, this option analyzes all indexes in the database, excluding Bitmap and Domain indexes, generates a non-selective indexes report and scripts to drop all non-selective indexes based on the report.

The index block selectivity is calculated using the following formula:

block selectivity for an index (BS) = (Average data blocks per index / number of data blocks in the table) * 100

An index is considered non-selective if the following condition is true:

BS > 15 or the number of objects in the table is less than 500.

If either of these conditions are met, an index is considered non-selective, and it will show up when you select the View Report tab in this page . You can drop all the non-selective indexes by selecting the Drop Indexes tab in this page.

Note: To generate a report and drop index script, you should analyze the entire database schema using the Tools>Analyze and select the Compute
Statistics option.

  • View the Non-Selective indexes Reports.

This option displays the Non-Selective Indexes report, which is generated
by the Generate option in this page.

  1. In the Recommendations to Drop-Generate page, click the View Report tab. The Recommendations to Drop - View Report page appears.

The columns displayed in the Recommendations to Drop-Generate page is described in the following table:
Column Description

Table Name

The DB Name of the class for which the Non Selective Indexes report has been generated.

Index Name

The name of the Non Selective Index.

Column Name

The DB Name of the property for which the Non Selective Indexes report has been generated.

Total Rows

The total number of rows that have analyzed.

Block Selectivity

The index block selectivity for this property.

 

Dropping Non-Selective Indexes

The Dropping Non-Selective Indexes allows you to execute the drop index statements, non-selective_index_drop.sql, generated using the Generate option. Before executing any of these scripts, review them and make changes if required.
This task explains how to drop the Non-selective Indexes:
  1. In the Recommendations to Drop page, click the Drop Indexes tab. Recommendations to Drop page - Drop Indexes page appears.
     

  2. Select one or more scripts from this page and click Drop to drop the selected Non
    Selective Indexes. The message All selected index(es) successfully dropped appears.
 

Dropping All Indexes

This functionality allows you to drop all indexes in the CES Database Administration.
This task explains how to drop all indexes:
  1. From the navigation pad, select Tools > Index Management > Drop All. The Drop All  page appears.

  2. You can choose to drop either User Indexes or Bitmap Indexes. After making your selection, click Drop. After the indexes are dropped, the column index type is reset to 0 (no index).
 

Dropping an Index

The Dropping an Index allows you to drop a specified user or bitmap index.
This task explains how to drop an index for a given class:
  1. In the navigation pad, select Tools > Index Management > Drop index for a Given
    Class
    . The Drop index for a Given Class page appears.

  2. Select the Class Label or Class Name to see a list of associated indexes for the
    class.

    The list of available indexes are displayed along with the details.

  3. Select the index and click Drop to drop the selected index.

    Note: After dropping the indexes, you must update the statistical information in the database by running Tools > Statistics option in CES.