Index maintenance in Oracle Database

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.

Select count (*) From dba_indexes Where tablespace_name = 'system' And owner not in ('sys ', 'system ') /

　　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:

Select segment_name, count (*) From dba_extents Where segment_type = 'index' And owner = upper ('& owner ') Group by segment_name /

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

Select Substr (segment_name, 1, 20) "segment name ", Bytes, Count (bytes) From dba_extents Where segment_name in (Select index_name From dba_indexes Where tablespace_name = upper ('& tablespace ')) Group by segment_name, bytes Order by segment_name /

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 */

Select count (first column of distinct | '%' | second column)/count (*) From table name /

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.

Analyze table name Compute statistics /

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

Select distinct_keys From user_indexes Where table_name = 'table name' And index_name = 'index name' /

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

Select num_rows From user_tables Where table_name = 'table name' /

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

Select I. distinct_keys/T. num_rows From User_indexes I, User_tables t Where I. table_name = 'table name' And I. index_name = 'index name' And I. table_name = T. table_name /

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:

Select Column_name, Num_distinct From user_tab_columns Where table_name = 'table name' /

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.

Validate index username. Index name /

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

Select Name, Del_lf_rows, Lf_rows, Round (del_lf_rows/(lf_rows + 0.0000000001) * 100) "frag percent" From index_stats /

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

Alter index username. Index name rebuild Tablespace name Storage (initial Initial Value: Next extension value) Nologging /

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

Alter index username. Index name coalesce /

(5) clear analysis information

Analyze index username. Index name Delete statistics /

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 */

Select count (first column of distinct | '%' | second column)/count (*) From table name /

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.

Analyze table name Compute statistics /

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

Select distinct_keys From user_indexes Where table_name = 'table name' And index_name = 'index name' /

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

Select num_rows From user_tables Where table_name = 'table name' /

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

Select I. distinct_keys/T. num_rows From User_indexes I, User_tables t Where I. table_name = 'table name' And I. index_name = 'index name' And I. table_name = T. table_name /

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:

Select Column_name, Num_distinct From user_tab_columns Where table_name = 'table name' /

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.

Validate index username. Index name /

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

Select Name, Del_lf_rows, Lf_rows, Round (del_lf_rows/(lf_rows + 0.0000000001) * 100) "frag percent" From index_stats /

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

Alter index username. Index name rebuild Tablespace name Storage (initial Initial Value: Next extension value) Nologging /

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

Alter index username. Index name coalesce /

(5) clear analysis information

Analyze index username. Index name Delete statistics /

 

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.

Select index_name
From dba_indexes
Where tablespace_name = 'system'
And owner not in ('sys ', 'system ')
/

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.

Set linesize 120
Col "owner" format A20
Col "Index" format A30
Col "table" format A30
Col "tablespace" format A30
Select
I. Owner "owner ",
I. index_name "Index ",
T. table_name "table ",
I. tablespace_name "tablespace"
From
Dba_indexes I,
Dba_tables t
Where I. Owner = T. Owner
And I. table_name = T. table_name
And I. tablespace_name = T. tablespace_name
And I. Owner not in ('sys ', 'system ')
/

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.

Col segment_name format A30
Select
Owner,
Segment_name,
Sum (bytes)
From dba_segments
Where tablespace_name = 'data table space name'
And segment_type = 'index'
Group by owner, segment_name
/

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.

Set linesize 100
Col owner format A10
Col segment_name format A30
Col tablespace_name format A30
Select
Count (*),
Owner,
Segment_name,
Tablespace_name
From dba_extents
Where segment_type = 'index'
And owner not in ('sys ', 'system ')
Group by owner, segment_name, tablespace_name
Having count (*)> 10
Order by count (*) DESC
/
 

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

Set linesize 120
Col "Index" format A30
Col "tablespace" format A20
Select
Owner "owner ",
Segment_name "Index ",
Tablespace_name "tablespace ",
Bytes "Bytes/count ",
Sum (bytes) "Total Bytes ",
Round (sum (bytes)/(1024*1024), 0) "total m ",
Count (bytes) "Total count"
From dba_extents
Where segment_type = 'index'
And segment_name in
(
'Index name 1 ',
'Index name2 ',
......
)
Group by owner, segment_name, segment_type, tablespace_name, bytes
Order by owner, segment_name
/

(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.

Select round (Bytes/(1024*1024), 2) Free (m)
From SM $ ts_free
Where tablespace_name = 'tablespace name'
/

(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.

Alter Index name
Rebuild
Tablespace index tablespace name
Storage (initial Initial Value: Next extension value)
Nologging
/

(5) Check the index.

Check the re-built index.

Select *
From dba_extents
Where segment_name = 'index name'
/

(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.

Select *
From dba_ind_columns
Where index_name like 'table name %'
/

Then, query based on the corresponding index items.

Select *
From 'table name %'
Where ......
/

(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.

Select 'alter tablespace' | tablespace_name | 'coalesce ;'
From dba_free_space_coalesced
Where percent_blocks_coalesced! = 100
/

Partition the tablespace.

Alter tablespace name coalesce
/