Rebuild Index ?

[vijay8282]

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

[SANJAY_G]

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

[Sameer]

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

[vijay8282]

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

[SANJAY_G]

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]