This article only discusses the most common index in Oracle, ie 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 installing the database, which contains all the data dictionaries, stored procedures, packages, functions, and triggers definitions, and system returns.
In general, you should try to avoid storage of non-SYSTEM users in the System table. Because there will be many problems with database maintenance and management. Once the system table is corrupted, only the database can be regenerated. We can use the following statements to check that there is no index of other users in the System table.
Select Count (*) from DBA_Indexes
Where tablespace_name = "system"
And Owner Not in ("Sys", "System")
2. Indexed storage
Oracle assigns logical structural spaces for all data in the database. The unit of the database space is a block, a range (extent), and segment.
Oracle Data Block is the minimum storage unit for Oracle use and assignment. It is determined by DB_BLOCK_SIZE set when it is established by the database. Once the database is generated, the size of the data block cannot be changed. To change the database can only be re-established. (There are some differences in Oracle9i, but this is not within the scope of this article.)
Extent is made up of a set of continuous blocks. One or more extents make up a segment. When all spaces in a segment are running, Oracle assigns a new extent.
Segment is made up of one or more extents. It contains all the data of a specific logical storage structure in a table space. Extents in a segment can be discontinuous, even in different data files.
An Object can only correspond to a logical stored segment, we can see the storage of the corresponding Object by viewing Extent in this segment.
(1) View the number of extents in the index section:
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 in the table space:
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 ("& Table Space"))
Group by segment_name, bytes
ORDER by segment_name
/
III. Selectability of the index
The selectivity of the index is the ratio of the number of different values in the column and the number of records recorded in the table. If there are 2000 records in a table, the description has 1980 different values, then the selectivity of this index is 1980/2000 = 0.99.
The closer the selectivity of an index, the higher the efficiency of this index.
If you use COST-based optimization, the optimizer should not use the selective index. If you use Rule-based optimization, the optimizer does not consider the selectivity of the index (unless the unique index) when determining the execution path, and has to manually optimize the query to avoid using non-selective indexes. Determine the selectivity of the index, there can be two ways: manual measurement and automatic measurement.
(1) Selectability of manual measurement index
If you want to create two columns in two columns according to a table, you can measure the selectivity of the index by using the following method:
Column selectivity = number of different values / total number of lines / closer to 1, better * /
SELECT Count (Distinct First Column || "%" || Second List) / count (*) from the name
If we know the selectivity of one of the indexes (for example, one of the primary keys), then we can know the selectivity of the other column index.
The advantage of the manual method is that the selectivity of the index can be evaluated before creating an index.
(2) Automatic measurement index selectivity
If you analyze a table, you will automatically analyze the index of all tables.
First, in order to determine the determinism of a table, the table is analyzed.
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 banks in the table:
SELECT NUM_ROWS from user_tables where table_name = "Table name"
Fourth, the selectivity of the index = the total number of different keywords / tables in the index:
SELECT I.DISTINCT_KEYS / T.NUM_ROWS from user_indexes I, user_tables twhere ostable_name = "Table name" and I.index_name = "index name" and i.table_name = T.TABLE_NAME
Fifth, you can query USER_TAB_COLUMNS for selectivity of each column.
Number of different values in the table in the table:
SELECT Column_name, Num_Distinct from user_tab_columns where table_name = "Table Name"
Column selectivity = Num_DistINCT / Total number of rows, query user_tab_columns helps measuring selectivity of each column, but it does not accurately measure the selectivity of column concurrent combinations. To measure the selectivity of a set of columns, you need to use a manual method or create an index and re-analyze the table according to this column.
IV. Determine the actual fragmentation of the index
As the database is used, the basic table is inevitably inserted, updated, and deleted, thus causing the leaf row to be deleted in the index, making the index produce fragments. The more frequently inserted the delete, the higher the degree of index debris. The generation of fragments increases access and use the I / O cost of the index. The high index of the debris must be reconstructed to maintain the best performance.
(1) Verify the index to verify the index with the verification index command.
This fills the valuable index information into the index_stats table.
Validate INDEX User Name. Index Name
(2) Query the index_stats table to determine the percentage of the unfilled leaf row 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 debris of the index is more than 10%, consider reconstructing the index in progress.
Alter Index User Name. Index Rebuild
TABLESPACE table space name
Storage (Initial initial value Next extension)
NOLOGGING
(4) If you can reconstruct the index if it is for space or other consideration, you can organize the index.
Alter Index User Name. Index Coalesce
(5) Clear analysis information
Analyze Index User Name. Index Name Delete Statistics
V. Reconstruction Index
(1) Check the index that needs to be rebuilt
The inspection is performed in accordance with the following aspects to determine the index that needs to be reconstructed.
First, check the user index in the System table space
To avoid fragmentation of the data dictionary, try to avoid the user's table and index in the System tablespace.
SELECT INDEX_NAME from DBA_INDEXES
WHERE TABLESPACE_NAME = "System" and Owner Not in ("Sys", "System")
Second, make sure the user's table and index are not in the same table space
The first rule of the table and index object is to separate the table and index. Built the table and the corresponding index in different tablespaces, preferably on different disks. This avoids many I / O conflicts that appear during data management and queries.
Set linesize 120
Col "Owner" Format A20
Col "index" Format A30
Col "Table" Format A30
Col "TableSpace" Format A30
Select I.WNER "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 which indexes in the data sheet space
The user's default table space should not be a system table space, but a data table space. When establishing an index, if the corresponding index table space name is not specified, the index is built in the data table space. This is a problem that programmers often ignore. When the index should be established, the corresponding index table space should be clearly indicated.
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 is extended more than 10 times
As the table record increases, the corresponding index is also increased. If an indexed next extent value setting is unreasonable (too small), the extension of the index segment becomes very frequent. The indexed extent is too much, 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 identifying 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 1", "Index Name 2", ...)
Group by owner, segment_name, segment_type, tablespace_name, Bytes
Order by ooner, segment_name
/
(3) Determine the remaining space in the index table space
Be sure to rebuild the index to which index table space. To ensure that the corresponding index table space has sufficient surplus space.
SELECT ROUND (Bytes / (1024 * 1024), 2) Free (M)
From sm $ ts_free
Where tablespace_name = "Table Space Name"
/
(4) Reconstruction index
Pay attention to the following points when reconstructing the index:
a. If you do not specify a TABLESPACE name, the index will be built in the user's default table space. b. If you do not specify NOLGGING, you will write the log, resulting in slowing the speed. Since the index is necessary, there is no necessary to recover, so you can write a log. c. If there is a source of resources, it indicates that the process is using the index and waits for a while.
Alter Index Index Rebuild
TABLESPACE Index Table Space Name
Storage (Initial initial value Next extension)
NOLOGGING
/
(5) Check the index
Check the rebuild index.
Select * from DBA_EXTENTS Where segment_name = "Index Name"
(6) Inquiry according to the index, check if the index is valid
Use the corresponding WHERE condition to make queries to ensure use of this index. See how the effect after using the index. Select * from DBA_IND_COLUMNS WHERE INDEX_NAME LIKE "Table Name"
Then, the query is performed according to the corresponding index item.
Select * from "Table%" Where ...
(6) Find out the tablespace with fragments and collect their fragments.
After reconstructing the index, the original index is deleted, which will cause the debris of the table space.
SELECT "alter TableSpace" || TableSpace_name || "coalesce"
From dba_free_space_coalesced
Where percent_blocks_coalent! = 100
/
Fragment of the table space.
Alter TableSpace Table Space Name Coalesce
