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 statistics在创建的同时收集CBO的统计信息。例：
SCOTT @devcedb>alter index IND_OBJ_ID rebuild online nologging parallel 4 compute statistics;
Index altered.
SCOTT @devcedb>select INDEX_NAME,LOGGING,DEGREE from user_indexes where INDEX_NAME='IND_OBJ_ID';
INDEX_NAME LOG DEGREE
------------------------------ --- ----------------------------------------
IND_OBJ_ID NO 4
SCOTT @devcedb>alter index IND_OBJ_ID logging parallel 1;
Index altered.
2)coallesce索引
alter index INDEXNAME coalesce;
SCOTT @devcedb>alter index ind_obj_id rebuild;
Index altered.
SCOTT @devcedb>analyze index ind_obj_id validate structure;
Index analyzed.
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 256 0 1140867 0 0
附：
INDEX_STATS
| Column |
Datatype |
NULL |
Description |
| HEIGHT |
NUMBER |
|
Height of the B-Tree |
| BLOCKS |
NUMBER |
NOT NULL |
Blocks allocated to the segment |
| NAME |
VARCHAR2(30) |
NOT NULL |
Name of the index |
| PARTITION_NAME |
VARCHAR2(30) |
|
Name of the partition of the index which was analyzed. If the index is not partitioned, NULL is returned. |
| LF_ROWS |
NUMBER |
|
Number of leaf rows (values in the index) --包含已打上delete标识的行数 |
| LF_BLKS |
NUMBER |
|
Number of leaf blocks in the B-Tree --包含空叶块 |
| LF_ROWS_LEN |
NUMBER |
|
Sum of the lengths of all the leaf rows |
| LF_BLK_LEN |
NUMBER |
|
Usable space in a leaf block |
| BR_ROWS |
NUMBER |
|
Number of branch rows in the B-Tree |
| BR_BLKS |
NUMBER |
|
Number of branch blocks in the B-Tree |
| BR_ROWS_LEN |
NUMBER |
|
Sum of the lengths of all the branch blocks in the B-Tree |
| BR_BLK_LEN |
NUMBER |
|
Usable space in a branch block |
| DEL_LF_ROWS |
NUMBER |
|
Number of deleted leaf rows in the index |
| DEL_LF_ROWS_LEN |
NUMBER |
|
Total length of all deleted rows in the index |
| DISTINCT_KEYS |
NUMBER |
|
Number of distinct keys in the index (may include rows that have been deleted) |
| MOST_REPEATED_KEY |
NUMBER |
|
How many times the most repeated key is repeated (may include rows that have been deleted) |
| BTREE_SPACE |
NUMBER |
|
Total space currently allocated in the B-Tree |
| USED_SPACE |
NUMBER |
|
Total space that is currently being used in the B-Tree |
| PCT_USED |
NUMBER |
|
Percent of space allocated in the B-Tree that is being used --（USED_SPACE/BTREE_SPACE)*100 |
| ROWS_PER_KEY |
NUMBER |
|
Average number of rows per distinct key (this figure is calculated without consideration of deleted rows) |
| BLKS_GETS_PER_ACCESS |
NUMBER |
|
Expected number of consistent mode block reads per row, assuming that a randomly chosen row is accessed using the index. Used to calculate the number of consistent reads that will occur during an index scan. |
| PRE_ROWS |
NUMBER |
|
Number of prefix rows (values in the index) |
| PRE_ROWS_LEN |
NUMBER |
|
Sum of lengths of all prefix rows |
| OPT_CMPR_COUNT |
NUMBER |
|
Optimal key compression length |
| OPT_CMPR_PCTSAVE |
NUMBER |
|
Corresponding space sa |