Maintenance of Oracle database indexes
- 2020-05-17 06:49:57
- OfStack
This article discusses only the most common index in Oracle, which is the B-tree index. The database version covered in this article is Oracle8i.
1. View the user index in the system table
In Oracle, the SYSTEM table is automatically created when you install the database and contains the entire database data dictionary, definitions of stored procedures, packages, functions, and triggers, and system rollback segments.
1 in general, you should try to avoid storing objects in the SYSTEM table that are not SYSTEM users. Because this will bring a lot of problems of database maintenance and management. Once the SYSTEM table is corrupted, the database can only be rebuilt. We can use the following statement to check if there are any other user indexes in the SYSTEM table.
2. Check the storage condition of the index
Oracle allocates logical structure space for all data in the database. The units of a database space are data blocks (block), scopes (extent), and segments (segment).
The Oracle data block (block) is the smallest storage unit used and allocated by Oracle. It is determined by DB_BLOCK_SIZE, which is set when the database is created. Once the database is generated, the size of the data block cannot be changed. The only way to change is to reestablish the database. (there is a difference in Oracle9i, but that is outside the scope of this article.)
Extent is made up of a continuous group of block. One or more extent forms one segment. When all space in an segment is used up, Oracle allocates a new extent to it.
Segment is made up of one or more extent. It contains all the data for a particular logical storage structure in a table space. extent in 1 segment can be discontinuous or even in different data files.
One object can only correspond to one logical storage segment. We can see the storage of the corresponding object by looking at extent in the segment.
(1) see the number of extent in the index segment:
(2) check the index expansion in the table space:
3. Index selectivity
Index selectivity is the ratio of the number of different values in the index column to the number of records in the table. If there are 2000 records in a table and 1980 different values in the table index columns, the index selectivity is 1980/2000=0.99.
The closer the selectivity of an index is to 1, the more efficient the index will be.
If you are using an cost-based optimization, the optimizer should not use poorly selected indexes. If rule-based optimization is used, the optimizer does not consider index selectivity (unless it is a unique index) when determining the execution path, and has to manually optimize the query to avoid using non-selective indexes.
There are two ways to determine index selectivity: manual measurement and automatic measurement.
(1) manual measurement of index selectivity
If you want to create a two-column collocated index based on two columns in a table, you can measure index selectivity by:
Column selectivity = number of different values/total number of rows /* the closer to 1, the better */
If we know the selectivity of the index for one of the columns (for example, if one of the columns is a primary key), then we can know the selectivity of the index for the other column.
The advantage of the manual approach is that you can evaluate index selectivity before creating the index.
(2) automatic measurement of index selectivity
If you analyze one table, the indexes of all tables are automatically analyzed as well.
First, in order to determine the certainty of a table, the table must be analyzed.
Second, determine the number of different keywords in the index:
Third, determine the total number of rows in the table:
4. Index selectivity = number of different keywords in the index/total number of rows in the table:
Fifth, you can query USER_TAB_COLUMNS to see the selectivity of each column.
The number of different values of all the rows in the table in this column:
Column selectivity =NUM_DISTINCT/ total number of rows in the table. The query USER_TAB_COLUMNS helps measure the selectivity of each column, but it does not accurately measure the selectivity of column collocations. To measure the selectivity of a set of columns, either manually or by creating an index and reanalyzing the table based on that set of columns.
4. Determine the actual fragmentation of the index
With the use of the database, it is inevitable to insert, update, and delete the base table, which results in the leaf rows being dropped from the index, resulting in fragmentation of the index. The more frequently a table is inserted and deleted, the greater the degree of index fragmentation. The generation of fragmentation adds to the cost of accessing and using I/O for this index. Indexes with high fragmentation must be rebuilt to maintain optimal performance.
(1) use the validation index command to verify the index.
This fills the index_stats table with valuable index information.
(2) query the index_stats table to determine the percentage of missing, unfilled leaf rows in the index.
(3) if the index has more than 10% leaf row fragments, consider rebuilding the index.
(4) if the index cannot be rebuilt due to space or other considerations, the index can be reorganized.
(5) clear the analysis information
[NextPage]
5. Rebuild the index
(1) check the index to be rebuilt.
Review the following to determine which indexes need to be rebuilt.
First, look at the user index in the SYSTEM table space.
To avoid data dictionary fragmentation, try to avoid user tables and indexes in the SYSTEM table space.
Second, make sure that the user's table and index are not in the same table space.
The first rule for table and index objects is to separate the table from the index. Build the table and the corresponding index in different table Spaces, preferably on different disks. This avoids the many I/O conflicts that occur during data management and queries.
Third, see what indexes are in the data table space
The default table space for the user should not be an SYSTEM table space, but a data table space. When an index is created, if the corresponding index tablespace name is not specified, then the index is built in the data tablespace. This is a problem that programmers often ignore. When creating an index, specify the corresponding index table space.
Fourth, see which index has been extended more than 10 times
As the number of table records increases, so does the number of indexes. If the next extent value of an index is not set properly (too small), the index segment will be extended frequently. Index extent too much, the speed and efficiency of retrieval will be reduced.
(2) after finding out the index that needs to be rebuilt, the size of the index needs to be determined to set reasonable index storage parameters.
(3) make sure there is enough space left in the index table space.
Determine which index tablespace to rebuild the index to. Make sure there is enough space left in the corresponding index table space.
(4) rebuild the index.
The following points should be noted when rebuilding the index:
a. If the tablespace name is not specified, the index will be built in the user's default table space.
b. If you do not specify nologging, the log will be written, resulting in a slow down. Since index reconstruction is not necessary for recovery, it is possible not to log.
c. If a resource is busy, it indicates that a process is using the index and wait 1 to commit again.
(5) check the index.
Check the rebuilt index.
(6) query according to the index and check whether the index is valid
Query using the corresponding where condition to ensure that the index is used. See what happens when you use the index.
The query is then based on the corresponding index entry.
(6) find out the table space with fragments, and collect its fragments.
After the index is rebuilt, the original index is dropped, resulting in fragmentation of the table space.
Clean up the fragmentation of the table space.
On 1 page