Index Rebuild

Check The Delete Leaf/Rows for the purpose of cosume Indexes & query take more time
after that rebuild when index usage more than 20.

* INDEX_STATS
* USER_INDEXES


select 'Analyze Index '||table_owner||'.'||index_name||' Validate Structure ; '
     from user_indexes
        where table_owner='SCOTT' order by index_name;

Select Name,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 as index_usage From index_stats Where LF_ROWS_LEN!=0 order by Name ;

spool c:\temp\indexusage.log
ANALYZE INDEX <index_name> COMPUTE STATISTICS;
Analyze Index scott.<index_name> Validate Structure ;

Select Name,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 as index_usage From index_stats Where LF_ROWS_LEN!=0 order by Name ;
ANALYZE INDEX <index_name> COMPUTE STATISTICS;

Analyze Index scott.<index_name> Validate Structure ;

Select Name,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 as index_usage From index_stats Where LF_ROWS_LEN!=0 order by Name ;
ANALYZE INDEX <index_name> COMPUTE STATISTICS;

Analyze Index scott.<index_name> Validate Structure ;

Select Name,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 as index_usage From index_stats Where LF_ROWS_LEN!=0 order by Name ;
ANALYZE INDEX <index_name> COMPUTE STATISTICS;

Analyze Index scott.<index_name> Validate Structure ;

Select Name,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 as index_usage From index_stats Where LF_ROWS_LEN!=0 order by Name ;
ANALYZE INDEX <index_name> COMPUTE STATISTICS;

Analyze Index scott.<index_name> Validate Structure ;

Select Name,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 as index_usage From index_stats Where LF_ROWS_LEN!=0 order by Name ;
spool off;


alter index scott.<index_name> rebuild ;




alter index scott.<index_name> rebuild online;


ALTER INDEX YYYYY REBUILD TABLESPACE INDX;