Oracle索引片段檢查及定期重建常用表的索引

標籤：add   last   oracle索引   技術   資訊   images   let   exit   splay   

轉載地址：http://www.cnblogs.com/zhaoguan_wang/p/5169821.html

背景說明：

       今天查閱書籍時，偶然間發現“在對某個索引行執行刪除操作時，只是為該行增加了一個刪除標記，這個索引行並不會釋放它的儲存空間，Insert產生的新的索引行也不能被插入到該位置。索引列的修改過程其實是將對應的列值刪除，然後再插入新的列值（與資料行本身的修改是不一致的，這也正是我們盡量不使用修改頻繁的列來建立索引的原因）。所以，無論是插入、修改、刪除，都需要消耗儲存空間，增大B-Tree索引結構的深度，影響資料的查詢速度。尤其是刪除和修改，不僅造成了儲存空間的浪費，而且增加了掃描索引塊的數量”，這就是所謂的索引片段問題，建議定期對經常使用的表執行檢查和重建索引操作。

問題重現：

      經測試，收集統計資訊等操作，無法釋放索引刪除塊所佔用的儲存空間。

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) * 100, ‘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;

 

重建索引：

CREATE OR REPLACE TYPE strsplit_type AS TABLE OF VARCHAR2(32676);CREATE OR REPLACE FUNCTION strsplit(p_value VARCHAR2, p_split VARCHAR2 := ‘,‘) --usage: select * from table(strsplit(‘1,2,3,4,5‘)) RETURN strsplit_typePIPELINED IS v_idx       INTEGER; v_str       VARCHAR2(500); v_strs_last VARCHAR2(4000) := p_value;BEGIN LOOP  v_idx := instr(v_strs_last, p_split);  EXIT WHEN v_idx = 0;  v_str       := substr(v_strs_last, 1, v_idx - 1);  v_strs_last := substr(v_strs_last, v_idx + 1);  PIPE ROW(v_str); END LOOP; PIPE ROW(v_strs_last); RETURN;END strsplit;

CREATE OR REPLACE PROCEDURE UP_CHECK_TO_REBUILD_INDEX(       tbNames varchar)IS    sqlstr VARCHAR2(100);    idx_ratio INT;BEGIN           --DECLARE sqlstr VARCHAR2(100);    --        idx_ratio INT;    BEGIN        FOR idx IN (SELECT t.index_name FROM user_indexes t                     WHERE t.index_type = ‘NORMAL‘ AND t.status = ‘VALID‘ AND t.temporary = ‘N‘ AND t.leaf_blocks > 100                        AND t.table_name IN (SELECT UPPER(TRIM(COLUMN_VALUE)) from table(strsplit(tbNames))) --//(‘TKK29‘)                    ORDER BY t.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‘;            EXECUTE IMMEDIATE sqlstr;                        SELECT TRUNC((t.del_lf_rows/t.lf_rows) * 100) INTO idx_ratio             FROM index_stats t WHERE t.name=idx.index_name AND ROWNUM=1;                        IF (idx_ratio >= 15) THEN               DBMS_OUTPUT.put_line(‘    REINDEX ‘ || TO_CHAR(SYSDATE, ‘yyyy-MM-dd hh24:mi:ss‘) || ‘ ratio: ‘ || idx_ratio);               sqlstr := ‘ALTER INDEX ‘ || idx.index_name || ‘ REBUILD‘;               EXECUTE IMMEDIATE 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 INDEX START 2016-01-29 17:49:20 ratio: 50   IDX_TKK29_ACTUALPARTICIPANT ANALYZE START 2016-01-29 17:49:22   IDX_TKK29_COMPLETEDDATE 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 START 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: 50

 

備忘：

    真實情境請考慮索引列的修改、資料刪除的機率，結合表的資料量大小等多種因素制定合理的維護計劃；另外，分區表的不同分區應該有不同的策略。

Oracle索引片段檢查及定期重建常用表的索引