Shulou(Shulou.com)06/01 Report--

Indexes can also be fragmented over time, and oracle provides us with three ways to deal with this problem: rebuild the index, merge the index, and shrink the index. Each has its own characteristics. I'll learn to take notes here today.

First: rebuild the index:

Rebuild the index is actually very simple and practical alter index index_name rebuild; parameters can be used to rebuild the specified index, but note that when rebuilding the index, the corresponding objects will be locked, so you must pay attention to how to avoid rebuilding the index without affecting other business use? Then you can specify the online parameter, for example: alter index index_name rebuild online; specifies this parameter, it will not have any impact on other business access objects. In addition, sometimes we can also specify parallel indexing, but note that when you specify the parallel (degree interger) parameter, the parallelism will be stored in the index, as the speed of creating based on the number of hardware such as cpu does improve, but the parallel method will be used when executing the query, sometimes accompanied by waiting events such as: PX Deq Credit: send blkd Therefore, it should be considered whether paralle should be used to create the index.

Eg:

[sql] view plain copySQL > create index emp_idx1 on emp (empno) parallel (degree 8); Index created.SQL > select index_name,degree from user_indexes where table_name='EMP';INDEX_NAME DEGREE--EMP_IDX1 8

In addition, when we need to recreate the reverse key index, we need to specify the reserver parameter: alter index index_name rebuild reverse; reclaims unused space when using the

Alter index index_name deallocate unused

Command, unused free segments will be returned to the data, but empty blocks that have been used will not be returned to the database space (including previously deleted indexes or unused space within the segment caused by moving entries). View the information about partitioned indexes and index organization tables:

Http://blog.csdn.net/rhys_oracle/article/details/18671897

Http://blog.csdn.net/rhys_oracle/article/details/18409063

In addition, how to determine whether the index needs to be rebuilt? It is generally believed that there are two situations:

1. Index depth is greater than or equal to 4

2. Deleted index entries account for 20% of the total index entries

3. Index space utilization is less than 50%.

Once again, we have to mention a view index_stats that does not have any data by default. When we use analyze index index_name validate structure; to analyze the index structure, we will fill in the corresponding data. Generally, this view can provide us with enough information to guide us whether we need to rebuild the index.

View relevant field information:

[sql] view plain copySQL > desc index_stats;Name Null? Type---HEIGHT NUMBER (for index height) BLOCKS NUMBER (number of blocks occupied by index) NAME VARCHAR2 (30) (index name) PARTITION_NAME VARCHAR2 (30) (score Zone index name) LF_ROWS NUMBER (number of leaf rows) LF_BLKS NUMBER (number of leaves in the b-tree index) LF_ROWS_LEN NUMBER (length of all leaf rows) LF_BLK_LEN NUMBER (free space in a leaf) BR_ROWS NUMBER (how many branches in the B-tree index) BR_BLKS NUMBER (how many branches in the B-tree index) BR_ROWS_LEN NUMBER (in the B-tree index) Total length of branched blocks) BR_BLK_LEN NUMBER (space available in the branch fast) DEL_LF_ROWS NUMBER (number of leaf rows deleted in the index) DEL_LF_ROWS_LEN NUMBER (total length of the number of leaf rows deleted in the index) DISTINCT_KEYS NUMBER (number of unique values including deleted rows) MOST_REPEATED_KEY NUMBERBTREE_SPACE NUMBER (total space currently allocated to the index) USED_SPACE NUMBER (has been The space used by the index includes the number of rows deleted) PCT_USED NUMBER (index space usage) ROWS_PER_KEY NUMBER (average number of rows per different key value does not include deleted rows) BLKS_GETS_PER_ACCESS NUMBERPRE_ROWS NUMBER (number of prefix rows) PRE_ROWS_LEN NUMBER (total length of prefix rows) OPT_CMPR_COUNT NUMBER (compressed length) OPT_CMPR_PCTSAVE NUMBERSQL >

Check the percentage of undeleted leaves as a percentage of the total number of rows. The formula is ((lf_rows-del_lf_rows) / lf_rows) * 100

To view the percentage of space occupied by undeleted rows, the formula is ((used_space-del_lf_rows_len) / btree_space) * 100

Pct_used calculation formula is: (used_space/btree_space) * 100

Eg:

[sql] view plain copySQL > create table test as select rownum id,'Amy' text from dual connect by level select count (*) from test;COUNT (*)-10000SQL > create index test_idx1 on test (id); Index created.SQL > select * from index_stats;no rows selectedSQL > analyze index test_idx1 validate structure Index analyzed.SQL > R1 select height,2 lf_rows,3 lf_blks,4 del_lf_rows,5 btree_space,6 used_space,7 pct_used,8 ((used_space-del_lf_rows_len) / btree_space) pct_unused 9 ((lf_rows-del_lf_rows) / lf_rows) pct_undel_rows10 from index_stats11 where name = 'TEST_IDX1' 12*HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED PCT_UNUSED PCT_UNDEL_ROWS-- -2 10000 21 0 175944 150021 86. 852663347 1SQL > SQL > analyze index test_idx1 validate structure Index analyzed.SQL > select height,2 lf_rows,3 lf_blks,4 del_lf_rows,5 btree_space,6 used_space,7 pct_used,8 ((used_space-del_lf_rows_len) / btree_space) pct_unused,9 ((lf_rows-del_lf_rows) / lf_rows) pct_undel_rows10 from index_stats11 where name = 'TEST_IDX1' HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED PCT_UNUSED PCT_UNDEL_ROWS -2 10000 21 9999 175944 150021 86. 001329969 .0001SQL > SQL > alter index test_idx1 deallocate unused Index altered.SQL > analyze index test_idx1 validate structure;Index analyzed.SQL > select height,2 lf_rows,3 lf_blks,4 del_lf_rows,5 btree_space,6 used_space,7 pct_used,8 ((used_space-del_lf_rows_len) / btree_space) pct_unused,9 ((lf_rows-del_lf_rows) / lf_rows) pct_undel_rows10 from index_stats11 where name = 'TEST_IDX1' HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED PCT_UNUSED PCT_UNDEL_ROWS -2 10000 21 9999 175944 150021 86. 001329969 .0001SQL >

Collect statistics, and then you can see the index leaf blocks that still exist in dba_indexes, and the optimizer uses the index.

[sql] view plain copySQL > exec dbms_stats.gather_table_stats ('SYS','TEST',cascade= > true); PL/SQL procedure successfully completed.SQL > select index_name,leaf_blocks,num_rows,degree from dba_indexes where index_name='TEST_IDX1' INDEX_NAME LEAF_BLOCKS NUM_ROWS DEGREE-- TEST_IDX1 1 1 1SQL > set autotrace trace expsSQL > SQL > select * from test where id select * from test ID TEX- 10000 AmySQL > insert into test values; insert into test values (10001 Japanese SYS) * ERROR at line 1:ORA-12899: value too large for column "SYS". "TEST". "TEXT" (actual: 4, maximum: 3) SQL > desc testName Null? Type-- -ID NUMBERTEXT CHAR (3) SQL > alter table test modify text char (15) Table altered.SQL > insert into test values (10001); 1 row created.SQL > commit;Commit complete.SQL > select height,2 lf_rows,3 lf_blks,4 del_lf_rows,5 btree_space,6 used_space,pct_used,7 8 ((used_space-del_lf_rows_len) / btree_space) pct_unused,9 ((lf_rows-del_lf_rows) / lf_rows) pct_undel_rows10 from index_stats11 where name = 'TEST_IDX1' HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED PCT_UNUSED PCT_UNDEL_ROWS -2 10000 21 9999 175944 150021 86. 001329969 .0001SQL > analyze index test_idx1 validate structure Index analyzed.SQL > select height,2 lf_rows,3 lf_blks,del_lf_rows,4 5 btree_space,6 used_space,pct_used,7 8 ((used_space-del_lf_rows_len) / btree_space) pct_unused,9 ((lf_rows-del_lf_rows) / lf_rows) pct_undel_rows10 from index_statswhere name = 'TEST_IDX1' 11HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE PCT_USED PCT_UNUSED PCT_UNDEL_ROWS -2 9584 21 9582 175944 143786 82. 001420907 .000208681SQL >

From the above, we can see that the remaining space after the index data is generated will not be returned to the database, but it will be possible to reuse the previously deleted data space when inserting new data. Another point can be seen that del_lf_row has already evaluated that there is an error. So far, it has just started to delete 9999 pieces of data, and then insert a piece of data for analysis, so now since it is 9582. Therefore, we cannot rely solely on del_lf_rows for index reconstruction evaluation. I remember that a friend once raised such a question, saying that the implementation of a sql in the test environment library will be very block, but it is very slow to lead to the formal environment, but the execution plan is all the same. My suspicion is that the index of the formal environment library needs to be rebuilt. Therefore, if it is determined that a large number of deletions have been performed on the same part of the index, resulting in a large number of index fragments, and the query reads a large number of index rows each time, and the index is used frequently, it is valuable to rebuild the index.

The second kind: merge indexes

Merging an index is to integrate and reorganize the free space in the adjacent index blocks in the index segment, thus freeing up the index block space, which is similar to our windows disk defragmentation, but note that the process will not return the free space to the database, but will add the free space to the list of free space so that it can be used next time. This operation is of great value for tables with sequence or time log fields, because when we delete most of the data on these tables, then a lot of space can no longer be used. then when we make a predicate query, we usually scan a lot of empty fast in the index, then the merged index will release the empty index block and the free list of the index block.

The statement is very simple:

Alter index index_name coalesce

Merging indexes is different from rebuilding indexes. Merging indexes does not reduce the height of the index, but reorganizes and integrates its data entries, but rebuilding may reduce the height of the index. In addition, rebuilding the index requires 2 times the disk space. First, you need to store the original index entry data, and you also need additional space to store the newly adjusted index data until the reconstruction is completed.

Note: merging indexes is an online operation.

Third: shrink index:

Because shrink is a relatively resource-consuming process, there are two processes, one is compact parameters, the other is direct shrink space, the first is similar to coalesce but will generate more redo logs and will not release space after execution, but shrink space can also free space to tablespaces in addition to defragmentation, but shrink space can still generate over-hit redo logs although it can be done online. In addition, shrink space also initiates line movement.

Eg:

Alter index index_name shrink space compact

Alter index index_name shrink space

Note: Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM).

Select name

Blocks

Del_lf_rows_len

Lf_rows_len

(del_lf_rows_len / lf_rows_len) * 100

(DEL_LF_ROWS / LF_ROWS) * 100

From index_stats

NAME BLOCKS DEL_LF_ROWS_LEN LF_ROWS_LEN (DEL_LF_ROWS_LEN / LF_ROWS_LEN) * 100 (DEL_LF_ROWS / LF_ROWS) * 100

IND_OBJ_ID 384 766085 1906952 40.1732713 40.2394062

Index fragmentation ratio: (del_lf_rows_len / lf_rows_len) * 100. if the percentage exceeds 20%, the index fragmentation ratio is very high. Needs to be defragmented.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.