Maintenance of indexes in Oracle database

This article only discusses the most common indexes in Oracle, that is, the B-tree index. The database version covered in this article is oracle8i.

I. View the user index in the system table

In Oracle, the system table is created automatically when the database is installed, containing all of the database's data dictionaries, the definitions of stored procedures, packages, functions, and triggers, and the system rollback segment.

In general, you should try to avoid storing objects in the system table that are not system users. This can lead to many problems with database maintenance and management. Once the system table is corrupted, only the database can be rebuilt. We can use the following statement to check that there are no other user's indexes in the system table.

Select COUNT (*)
From Dba_indexes
where tablespace_name = ' SYSTEM '
and owner not in (' SYS ', ' SYSTEM ')
/

Two. Index storage check

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

Oracle Data blocks (block) are the smallest storage unit used and allocated by Oracle. It is determined by the Db_block_size set when the database was established. Once the database is generated, the size of the data block cannot be changed. To change, you can only re-establish the database. (There are some differences in oracle9i, but this is not covered in this article.) ）

The extent is made up of a set of contiguous blocks. One or more extent form a segment. When all the space in a segment is exhausted, Oracle assigns it a new extent.
　
Segment is made up of one or more extent. It contains all the data for a particular logical storage structure in a table space. The extent in one segment can be discontinuous, even in different data files.

An object can only correspond to the segment of a logical store, we can see the storage of the corresponding object by looking at the extent in the segment.

(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 the index within 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
/


Three. Selectivity of index

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

The closer the selectivity of an index is to 1, the higher the efficiency of the index.

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

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

(1) Manual measurement of Index selectivity

If you want to create two columns and an index based on two columns of a table, you can measure the selectivity of the index in the following ways:

Column selectivity = number of different values/total number of rows

Select COUNT (distinct first column | |) %'|| Second column)/count (*)
From table name
/

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

The advantage of manual methods is that you can evaluate the selectivity of indexes before creating an index.

(2) Automatic measurement of index selectivity

If you parse a table, the indexes for all tables are also automatically parsed.

First, in order to determine the certainty of a table, it is necessary to analyze the 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 selectivity of the index = 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
/

Five, you can query User_tab_columns to understand the selectivity of each column.

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

Select
COLUMN_NAME,
Num_distinct
From User_tab_columns
Where table_name= ' table name '
/

The selectivity of the column =num_distinct/the total number of rows in the table, and the query User_tab_columns helps measure the selectivity of each column, but it does not accurately measure the selectivity of the combination of columns. To measure the selectivity of a set of columns, you need to use manual methods or create an index based on this set of columns and then parse the table again.

Four. Determine the actual fragmentation of the index

As the database is used, the basic tables are inevitably inserted, updated, and deleted, causing the leaf rows to be deleted in the index, causing the index to fragment. The more frequently deleted tables are inserted, the higher the index fragmentation is. The creation of fragmentation increases the I/O cost of accessing and using the index. Higher-fragmentation indexes must be rebuilt to maintain optimal performance.

(1) Verify the index using the verification Index command.

This fills the Index_stats table with valuable index information.

Validate index username.
/

(2) Query the Index_stats table to determine the percentage of rows of leaves that are 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) Consider rebuilding the index if the leaf row of the index is more than 10% fragments.

Alter index user name. Index name rebuild
Tablespace Table Space Name
Storage (initial initial value next extended value)
Nologging
/

(4) If the index cannot be rebuilt for space or other consideration, the index can be collated.

Alter index user name. Index name COALESCE
/

(5) Clear analysis information

Analyze index username.
Delete statistics
/

Five. Rebuilding indexes

(1) Check the index that needs to be rebuilt.

Check in the following areas to determine which indexes need to be rebuilt.

First, look at the user indexes in the system tablespace.

To avoid fragmentation of the data dictionary, try to avoid the presence of users ' tables and indexes in the system table space.

Select Index_name
From Dba_indexes
where Tablespace_name= ' SYSTEM '
and owner not in (' SYS ', ' SYSTEM ')
/

Second, make sure that the user's tables and indexes are not in the same table space.

The first rule for tables and index objects is to separate tables and indexes. The tables and corresponding indexes are built in different table spaces, preferably on different disks. This avoids many I/O conflicts that occur during data management and querying.

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, see what indexes are in the datasheet space

The user's default tablespace should not be the system table space, but the datasheet space. When indexing is established, the index is built into the datasheet space if the corresponding index table space name is not specified. This is a problem that programmers often overlook. When indexing should be in place, explicitly indicate the corresponding index tablespace.

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
/

Four, see which index has been expanded more than 10 times

As the table records increase, the corresponding index increases. If the next extent value of an index is set unreasonably (too small), the extension of the index segment becomes very frequent. There are too many extent in the index, and the speed and efficiency of the retrieval 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 you find the index that needs to be rebuilt, you need to determine the size of the index 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 name 2 ',
......
)
GROUP BY Owner,segment_name,segment_type,tablespace_name,bytes
ORDER BY Owner,segment_name
/

(3) To determine the index table space there is enough space left.

Determine which index table space to rebuild the index to. Make sure that the corresponding index table space has enough space left.

Select Round (bytes/(1024*1024), 2) free (M)
From Sm$ts_free
Where tablespace_name= ' table space name '
/

(4) Rebuilding the index.

The following points should be noted when rebuilding an index:

A. If you do not specify a tablespace name, the index is built in the user's default tablespace.

B. If you do not specify nologging, the log will be written, resulting in slower speeds. Because the rebuild of the index is not necessary to restore, you can not write the log.

C. If a resource is busy, it indicates that the process is using the index and waits for a later submission.

Alter INDEX index name
Rebuild
Tablespace Index Table space name
Storage (initial initial value next extended value)
Nologging
/

(5) Check the index.

Check for a rebuilt index.

SELECT *
From Dba_extents
Where segment_name= ' index name '
/

(6) query according to the index, check whether the index is valid

Use the appropriate where condition for the query to ensure that the index is used. See what happens when you use an index.

SELECT *
From Dba_ind_columns
Where index_name like ' table name% '
/

Then, the query is based on the corresponding index entry.

SELECT *
From ' Table name% '
where ...
/

(6) Locate the fragmented tablespace and collect its fragments.

Once the index is rebuilt, the original index is deleted, which can result in fragmentation of the table space.

Select ' Alter tablespace ' | | tablespace_name| | ' coalesce; '
From dba_free_space_coalesced
where percent_blocks_coalesced!=100
/

Defragment the table space.

Alter tablespace table space name coalesce