Rebuild Index ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Rebuild Index ?

  1. #1
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340
    Hi,

    I hv validated my indexes.

    Actually I analyzed Indexes based on 2 criteria.

    1. Size, top 15 Indexes
    2. No of Extents : Top 15 Indexes.

    Now I wanna know which indexes are a candidate for rebuilding.

    If I go by height of Indexes then lot of Indexes are a candidate. As Oracle says if Height > 3, rebuild that.

    Is it true.

    I am giving a sample of stats below :

    Levels of B*-Tree 4
    Blocks allocated 490,242
    Bytes allocated in tree 3,731,805,152
    Bytes used in tree 1,778,520,401
    Percentage used 48
    Leaf blocks 463,302
    Leaf rows 37,554,765
    Leaf rows length 1,765,073,955
    Deleted rows 333,188
    Distinct keys 37,554,765
    Most repeated key 1
    Block gets per access 5
    Rows per key 1
    Branch blocks 3,161
    Branch rows 463,301
    Branch rows length 13,446,446


    On Which fields should I concentrate to proceed further.

    Help..

    Regards
    Vijay

    --------------------------
    The Time has come ....

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    I rebuild the indexes if the height is more than 3 or
    the %ratio of (del_lf_rows / (del_lf_rows+lf_rows)) >25

    Sanjay

  3. #3
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Hi,

    Heres a procedure which checks if the index to be rebuild or not based on the values in INDEX_STATS...

    CREATE OR REPLACE PROCEDURE proc_Rebuild_Index IS

    sIndex USER_INDEXES.INDEX_NAME%TYPE;
    sStatement VARCHAR2(256);

    nHeight INDEX_STATS.HEIGHT%TYPE;
    ndRows INDEX_STATS.DEL_LF_ROWS%TYPE;
    nlfRow INDEX_STATS.LF_ROWS%TYPE;
    nRatio NUMBER(10,2);

    CURSOR cIndexName
    IS
    SELECT INDEX_NAME
    FROM USER_INDEXES;

    BEGIN

    OPEN cIndexName;
    LOOP

    FETCH cIndexName INTO sIndex;
    EXIT WHEN cIndexName%NOTFOUND;

    sStatement := 'ANALYZE INDEX ' || sIndex || ' VALIDATE STRUCTURE' ;
    EXECUTE IMMEDIATE sStatement;

    BEGIN

    SELECT Height,Lf_rows,Del_lf_rows,(Del_lf_rows/Lf_rows)*100 as Ratio
    INTO nHeight, nDRows, nlfRow, nRatio
    FROM INDEX_STATS
    WHERE NAME = sIndex;

    EXCEPTION
    WHEN ZERO_DIVIDE THEN
    NULL;
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SqlErrm);
    END;

    IF NVL(nRatio,0) > 20 THEN
    sStatement := 'ALTER INDEX ' || sIndex || ' REBUILD' ;
    EXECUTE IMMEDIATE sStatement;
    DBMS_OUTPUT.PUT_LINE('Recreated Index ' ||sIndex);
    END IF;

    END LOOP;

    CLOSE cIndexName;

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No Data Found');
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SqlErrm ||sStatement);
    END proc_Rebuild_Index;
    /


    Sameer

  4. #4
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340
    Hi,

    That is ok.

    What all precautins Do I need to take before going for rebuilding.

    in my case Heights is coming 4 for most of indexes analyzed by me.

    So I am planning to REBUID those.

    Regards
    Vijay
    --------------------------
    The Time has come ....

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    No major risk.
    Just watchout for the performance while you are rebuilding. You may consider using the options nologging, parallel and online.

    Sanjay

    [Edited by SANJAY_G on 07-23-2002 at 04:36 AM]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width