Oracle index fragmentation checking and regular rebuilding of common table indexes

Reprint Address: http://www.cnblogs.com/zhaoguan_wang/p/5169821.html

Background notes:

When you look at a book today, you occasionally find that "when you perform a delete operation on an index row, you add a delete tag to the row, and the index row does not free its storage space, and the new index row generated by the insert cannot be inserted into that location." The process of modifying an indexed column is to delete the corresponding column value, and then insert the new column value (which is inconsistent with the data row itself, which is why we try not to create the index with the frequently modified columns). Therefore, whether it is inserting, modifying, deleting, we need to consume storage space, increase the depth of B-tree index structure, and affect the query speed of data. In particular, the deletion and modification not only caused the waste of storage space, but also increased the number of scanned index blocks ", which is called the index fragmentation problem, it is recommended to periodically perform check and rebuild index operations on frequently used tables.

Problem Recurrence:

After testing, collect statistics and other operations, unable to release the storage space occupied by the index delete block.

Analyze table tkk29 compute statistics;

Select T.index_name, T.distinct_keys, T.num_rows, T.sample_size, t.last_analyzed, T.blevel, T.leaf_blocks, t.* From user_indexes t where T.table_name = upper (' tkk29 ');

Delete from tkk29 where mod (trunc ((sysdate-createddate) * 24 * 60), 2) = 0;

Analyze table tkk29 compute statistics;

Analyze index idx_tkk29_participant validate structure;

Select T.name, T.blocks, T.lf_rows, T.del_lf_rows, t.lf_rows-t.del_lf_rows as Lf_rows_used, To_char ((T.DEL_LF _rows/t.lf_rows) *, ' 999.999 ') as ratio, t.* from Index_stats t

Alter index idx_tkk29_participant rebuild;

Alter index idx_tkk29_actualparticipant rebuild;

Analyze index idx_tkk29_participant validate structure;

Rebuild Index:

CREATEORREPLACE TYPE Strsplit_typeAsTABLEOfVARCHAR2 (32676);CREATEORREPLACEFUNCTION Strsplit (P_valueVARCHAR2, P_splitVARCHAR2:=‘,‘)--Usage:select * FROM table (strsplit (' 1,2,3,4,5 '))RETURNstrsplit_typepipelinedIsV_idxINTEGER; V_strVARCHAR2 (500); V_strs_lastVARCHAR2 (4000):=P_value;BEGINLOOP V_idx:=InStr (V_strs_last, P_split);EXITWhen V_idx= 0; v _STR: = substr (v_strs_last, 1, V_idx Span style= "COLOR: #808080" >-1= substr (v_strs_last, v_idx + 1); pipe ROW (V_STR); end LOOP; pipe ROW (v_strs_last); return; END strsplit;                

CREATEORREPLACEPROCEDUREUp_check_to_rebuild_index (tbnamesvarchar)IsSqlstrVARCHAR2 (100); Idx_ratioInt;BEGIN--DECLARE sqlstr VARCHAR2 (100);--Idx_ratio INT;BEGINFor IDXIn (SELECT T.index_nameFromUser_indexes TWHERE T.index_type=‘NORMAL‘and T.status=‘VALID‘and T.Temporary=‘N‘and T.leaf_blocks>100and T.table_nameIn (SELECTUPPER (TRIM (Column_value))FromTable (Strsplit (tbnames)))--(' TKK29 ')ORDERByT.table_name, T.index_name) LOOP Dbms_output.put_line (idx.index_name||‘ANALYZE START‘|| To_char (Sysdate,‘YYYY-MM-DD Hh24:mi:ss‘)); SQLSTR:=‘ANALYZE INDEX‘|| Idx. Index_name||‘VALIDATE STRUCTURE‘;EXECUTEIMMEDIATE sqlstr;SELECT TRUNC (t.del_lf_rows/t.lf_rows)*100)IntoIdx_ratioFrom Index_stats tWHERE T.name=idx.index_nameand ROWNUM=1;IF (Idx_ratio>=15)ThenDbms_output.put_line (‘REINDEX‘|| To_char (Sysdate,‘YYYY-MM-DD Hh24:mi:ss‘)||‘Ratio‘||Idx_ratio); SQLSTR:=‘ALTER INDEX‘|| Idx.index_name||‘REBUILD‘;EXECUTEIMMEDIATE sqlstr;end if end LOOP; end; End Up_check_to_rebuild_index; Sql>exec up_check_to_rebuild_index ( ' tkk29, Muser" begin Up_check_to_rebuild_index (  "tkk29, Muser" end;             

Pk_muser ANALYZE start 2016-01-29 17:49:19 idx_tkk29_activityinstanceid ANALYZE START 2016-01-29 17:49:19 REBUILD in DEX start 2016-01-29 17:49:20 ratio:50 idx_tkk29_actualparticipant ANALYZE START 2016-01-29 17:49:22 idx_tkk29_comple Teddate ANALYZE start 2016-01-29 17:49:22 REBUILD INDEX START 2016-01-29 17:49:22 ratio:36 idx_tkk29_participant ANALYZE start 2016-01-29 17:49:23 idx_tkk29_processinstanceid ANALYZE start 2016-01-29 17:49:23 REBUILD INDEX STAR T 2016-01-29 17:49:24 ratio:50 idx_tkk29_stateddate ANALYZE start 2016-01-29 17:49:25 REBUILD INDEX start 2016-01 -29 17:49:25 ratio:33 pk_tkk29 ANALYZE start 2016-01-29 17:49:27 REBUILD INDEX START 2016-01-29 17:49:27 ratio:5 0

Note:

Real scene Consider the modification of the index column, the probability of data deletion, and the amount of data in the table to make a reasonable maintenance plan; In addition, different partitions of a partitioned table should have different policies.

Oracle index fragmentation checking and regular rebuilding of common table indexes