Oracleでインデックスのsqlを再構築する必要があるかどうかを確認します。

 
SELECT 
height, /*Height of the B-Tree*/ 
blocks, /* Blocks in the index segment */ 
name, /*index name */ 
lf_rows, /* number of leaf rows in the index */ 
lf_blks, /* number of leaf blocks in the index */ 
del_lf_rows, /* number of deleted leaf rows in the index */ 
rows_per_key /* average number of rows per distinct key */ 
blk_gets_per_access /* consistent mode block reads (gets) */ 
FROM INDEX_STATS 
WHERE NAME='INDEX_NAME'; 

 
ANALYZE index INDEX_NAME VALIDATE STRUCTURE 

HEIGHT：This column refers to the height of the B-tree index，and it's usualy the 1，2，or 3 level.If large inserts pussh the index height beyond a level of 4，it the strech.twith.it。LF_ROWS：This is the number of leaf nodes deleted due to the deletion of rows.Oracle doesn't rebuild indexes amatially and、consequently、too many deleted leafrows can lead an unbalanced B-tree.GETS_PER_ACCESS：You can look at the BLK_GETS_PER_ACCESS column to see how much logcal I/O it Taes to retrieve data from the index.If this row show a doub-digit number，you shound probably start reilding the index.