Oracle database index Maintenance

The ORACLE tutorial is: maintenance of Oracle Database indexes.

This article only discusses the most common index in Oracle, namely the B-tree index. The database version involved in this article is Oracle8i.

1. view User indexes in the system table

In Oracle, the SYSTEM table is automatically created when the database is installed. It contains all the data dictionaries of the database, definitions of stored procedures, packages, functions and triggers, and SYSTEM rollback segments.

In general, avoid storing non-SYSTEM user objects in the SYSTEM table. This will cause many problems in database maintenance and management. Once the SYSTEM table is damaged, you can only regenerate the database. We can use the following statement to check whether the indexes of other users exist in the SYSTEM table.


Ii. Check the storage of Indexes

Oracle allocates logical structure space for all data in the database. The unit of the database space is data block, range (extent), and segment (segment ).

Oracle data blocks are the minimum storage units used and allocated by Oracle. It is determined by the DB_BLOCK_SIZE set during database creation. Once the database is generated, the size of the data block cannot be changed. To change the database, you must create a new database. (There are some differences in Oracle9i, but this is not covered in this article .)

Extent is composed of a group of continuous blocks. One or more extents form a segment. When all space in a segment is used up, Oracle assigns it a new extent.

Segment is composed of one or more extent. It contains all data in a specific logical storage structure in a tablespace. The extent in a segment can be discontinuous or even in different data files.

An object can only correspond to one segment of logical storage. We can view the extent in this segment to see the storage of the corresponding object.

(1) view the number of extents in the index segment:


(2) view the expansion of indexes in the tablespace:

Iii. Selection of Indexes

The index selectivity refers to the ratio of the number of different values in the index column to the number of records in the table. If a table has 2000 records and the index column has 1980 different values, the selectivity of this index is 1980/2000 = 0.99.

The more selective an index is, the more efficient it is.

If cost-based optimization is used, the optimizer should not use poorly selective indexes. If rule-based optimization is used, the optimizer does not consider the index selectivity (unless it is a unique index) when determining the execution path, and has to manually optimize the query to avoid the use of non-selective indexes.

There are two ways to determine the index selectivity: manual measurement and automatic measurement.

(1) manually measuring index Selectivity

If you want to create two simultaneous indexes based on the two columns of a table, you can use the following method to measure the index selectivity:

Column selectivity = number of different values/total number of rows/* The closer the value is to 1, the better */


If we know the selectivity of one column of indexes (for example, one column is the primary key), we can know the selectivity of another column of indexes.

The advantage of the manual method is that the index selection can be evaluated before the index is created.

(2) Automatic Measurement of index Selectivity

If a table is analyzed, indexes of all tables are automatically analyzed.

First, an analysis table is required to determine the certainty of a table.


Second, determine the number of different keywords in the index:


Third, determine the total number of rows in the table:


Fourth, index selectivity = number of different keywords in the index/total number of rows in the table:


Fifth, You can query USER_TAB_COLUMNS to understand the selectivity of each column.

The number of different values of all rows in this column in the table:


Column selectivity = the total number of rows in the NUM_DISTINCT/table. querying USER_TAB_COLUMNS helps to measure the selectivity of each column, but it does not accurately measure the selectivity of column Union combinations. To measure the selectivity of a group of columns, You need to manually create an index based on the group of columns and re-analyze the table.

4. determine the actual fragmentation of the Index

With the use of the database, it is inevitable to insert, update, and delete the basic table. As a result, the leaf row is deleted from the index, causing fragmentation of the index. The more frequently the table is inserted and deleted, the higher the degree of index fragmentation. The generation of fragments increases the I/O cost for accessing and using the index. Indexes with high fragmentation must be rebuilt to maintain optimal performance.

(1) Use the verification index command to verify the index.

This will fill in valuable index information in the index_stats table.


(2) query the index_stats table to determine the percentage of leaf rows that are deleted and not filled in the index.


(3) If the fragmentation of the leaf row of the index exceeds 10%, rebuild the index.


(4) If the index cannot be rebuilt out of space or other considerations, you can sort the index.


(5) clear analysis information

[NextPage]

5. Re-Indexing

(1) Check the indexes to be rebuilt.

Check according to the following aspects to determine the index to be rebuilt.

First, view the user index in the SYSTEM tablespace.

To avoid Data Dictionary Fragments, try to avoid user tables and indexes in the SYSTEM tablespace.


Second, make sure that your table and index are not in the same tablespace.

The first rule for a table and an index object is to separate the table from the index. We recommend that you create tables and corresponding indexes in different tablespaces on different disks. This avoids many I/O conflicts during data management and query.


Third, check the indexes in the data table space.

The user's default tablespace should not be a SYSTEM tablespace, but a data table space. If the index tablespace name is not specified during index creation, the index is created in the data table space. This is a problem that programmers often ignore. The corresponding index tablespace should be explicitly specified during index creation.


Fourth, check which index has been extended more than 10 times.

As Table records increase, the corresponding indexes also increase. If the next extent value of an index is set improperly (too small), the index segment is extended frequently. If there are too many extent indexes, the retrieval speed and efficiency will be reduced.


(2) After finding the index to be rebuilt, You need to determine the index size to set reasonable index storage parameters.


(3) check whether there is sufficient space left in the index tablespace.

Determine the index tablespace to which the index is to be rebuilt. Make sure that the index tablespace has enough space.


(4) re-create the index.

Pay attention to the following points during index reconstruction:

A. If the tablespace name is not specified, the index is created in the user's default tablespace.

B. If nologging is not specified, logs will be written, resulting in slow speed. Because re-indexing is not necessary to restore the index, you do not need to write logs.

C. If the resource is busy, it indicates that a process is using the index. Wait for a while before submitting the index.


(5) Check the index.

Check the re-built index.


(6) query by index to check whether the index is valid

Use the corresponding where condition to query. Make sure that this index is used. See how the index works.


Then, query based on the corresponding index items.


(6) Identify the tablespace with fragments and collect the fragments.

After the index is re-created, the original index is deleted, which may cause table space fragmentation.


Partition the tablespace.

Previous Page