What are the most common indexes in Oracle databases?

In the maintenance of related indexes in Oracle databases, this article mainly discusses the most common related indexes in Oracle, namely, B-tree indexes. The database version is Oracle8i. The following is a description of the specific solution. I hope it will help you in your future study.

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

Oracle data block) is the minimum storage unit 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 extent 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 when determining the execution path unless it is a unique index) and has to optimize the query manually 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'
  
  
/
 
 

The above content is an introduction to index maintenance in the Oracle database. I hope you will find some gains.
 

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