Rebuild index in Production

I am going to rebuild all Production Database indexes with a batch job. I want to know that do I have to run one of these
commands after rebuild finish :

ANALYZE INDEX COMPUTE STATISTICS;

ANALYZE TABLE COMPUTE STATISTICS FOR ALL INDEXED COLUMNS


Also I want to know because couple of tables are really big is it any problem can happen? For example I think I need a big Temp Tablespace !! Please advice what safety cution I have to take into effect ?

You don't need to run analyze command separately.

In the "CREATE INDEX ...." script add compute statistics ;
This is faster than running the analyze command.

I don't use create index , I want to user "ALTER" INDEX ....REBUILD . Also what I have to choose for next and first extent ( Rule of thunmb ? )

Ensure you have created a big temp tablespace for sorting, if you are using CREATE INDEX statement.

Example for ALTER INDEX:


ALTER INDEX SALES_IDX REBUILD
TABLESPACE TS_IDX
STORAGE ( INITIAL 20M NEXT 20M PCTINCREASE 0)
PARALLEL NOLOGGING COMPUTE STATISTICS;

ALTER INDEX SALES_IDX LOGGING;


The INITIAL and NEXT value depend upon the table size.

Guidelines for rebuilding Index.

Find out indexes that are fragmented

1) select TABLESPACE_NAME , SEGMENT_NAME , COUNT(*)
from DBA_EXTENTS
where SEGMENT_TYPE = 'INDEX'
group by TABLESPACE_NAME , SEGMENT_NAME HAVING COUNT(*) > 5
ORDER BY 3 DESC

this SQL gives you indexs that are fragmented. Change the
group predicate condition if you wish


2) analyze index validate structure
will analyze the index structure

3) select * from index_stats

will display the computed statistics. Some significant fields are
HEIGHT, BLOCK ,
computed value for DEL_LF_ROWS /decode( LF_ROWS,0,1,LF_ROWS ))*100

( According to Oracle for any index, if HEIGHT > 4 or the computed ratio is greater than 25 then the index is a candidate for rebuild.)

4 )select name , value from v$parameter
where name = 'db_block_size' . This will give the value of
db_block_size.

5 ) Rebuild the index

alter index rebuild tablespace
strorage (initial comp_val1 , next comp_val2 , pctincrease 0 )

comp_val1 = BLOCK ( generated in step 3 ) * value of db_block_size
comp_val2 = comp_vale1/2


6 ) If needed create a PL SQL script to autmatically perform all these
steps and run it when the work load on the server is low.