How to maintain indexes in Oracle databases

The following articles mainly introduce the maintenance of indexes in Oracle databases. If you are curious about the actual operation and application of indexes in Oracle databases, the following articles will unveil its mysteries. I hope you will gain some benefits after browsing.

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
  
  
/
 
 

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.

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

The above content describes the items related to index maintenance in the Oracle database, hoping to help you in this regard.

Article by: http://database.51cto.com/art/200703/43583.htm