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_INDEXESWHERE 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_extentswhere segment_type = 'index'and owner = Upper (' & oowner ') Group by segment_name /
(2) View the expansion of the index in the table space:
selectsubstr (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, bytesorder by segment_name /
3. The selectivity of the selective index 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 FROM 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_keysfrom user_indexeswhere table_name = 'table name' and index_name = 'Index' /
Third, determine the total number of banks in the table:
SELECT NUM_ROWSFROM User_Tableswhere Table_name = 'Name' /
Fourth, the selectivity of the index = the total number of different keywords / tables in the index:
SELECT I.DISTINCT_KEYS / T.NUM_ROWSFROMUSER_INDEXES I, USER_TABLES Twhere i.table_name = '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:
SELECTCOLUMN_NAME, NUM_DISTINCTFROM user_TAB_COLUMNSWHERE TABLE_NAME = '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.
Selectname, DEL_LF_ROWS, LF_ROWS, ROUND ((DEL_LF_ROWS / (LF_ROWS / (LF_ROWS 0.0000000001)) * 100) "Frag Percent" from index_stats / (3) If the indexed leaf line is more than 10%, consider reconstructing the index.
ALTER INDEX User Name. Index Name RebuildTableSpace 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 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 tablespace.
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 120col "OWNER" format a20col "INDEX" format a30col "TABLE" format a30col "TABLESPACE" format a30selecti.owner "OWNER", i.index_name "INDEX", t.table_name "TABLE", i.tablespace_name "TABLESPACE" from DBA_INDEXES I, DBA_TABLES Twhere I.OWNER = T.OWNERAND I.TABLE_NAME = T.TABLE_NAMEAND I.TABLESPACE_NAME = T.TABLESPACE_NAMEAND 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 A30Select Owner, Segment_name, SUM (Bytes) from DBA_SEGMENTSWHERE 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 100col owner format a10col segment_name format a30col tablespace_name format a30selectcount (*), owner, segment_name, tablespace_namefrom dba_extentswhere segment_type = 'INDEX'and owner not in (' SYS ',' SYSTEM ') group by owner, segment_name, tablespace_namehaving count (* )> 10ORDER 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 120col "INDEX" format a30col "TABLESPACE" format a20selectowner "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_EXTENTSWHERE segment_type = 'index' and segment_name in ('index name 1', 'index number 2', ... ) Group by owner, segment_name, segment_type, tablespace_name, bytesRder by Owner, Segment_name /
(3) Determine the remaining space of 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_freewhere tablespace_name = 'table space name' /
(4) Reconstruction of the 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 Name RebuildTableSpace Index Table Space Name Storage (Initial Initial NEXT Extension) NOLOGGING /
(5) Check the index.
Check the rebuild index.
SELECT * from DBA_EXTENTSWHERE Segment_name = 'Index' /
(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_COLUMNSWHERE INDEX_NAME LIKE 'Table Name%' / then queries according to the corresponding index item.
SELECT * from '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_coalescedwhere percent_blocks_coalent! = 100 /
Fragment of the table space.
Alter TableSpace Table Space Name Coalesce /
