2、索引碎片整理
由于index_stats只能存储最近一行数据,analyze index后查询index_stats必须在同一session内,否则查询index_stats无记录,我们回到之前碎片很严重的索引ind_obj_id需要重新analyze一下:
SCOTT @devcedb>analyze index ind_obj_id validate structure;
Index analyzed.
Elapsed: 00:00:00.13
SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100 from index_stats;
NAME BLOCKS DEL_LF_ROWS_LEN LF_ROWS_LEN (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 (DEL_LF_ROWS/LF_ROWS)*100
------------------------------ ---------- --------------- ----------- --------------------------------- -------------------------
IND_OBJ_ID 384 766085 1906952 40.1732713 40.2394062
碎片整理的方式
1)重建索引
alter index INDEXNAME rebuild;
alter index INDEXNAME rebuild online;
index rebuild可以使用nologging减少redo的生成,parallel并行创建,compute