索引优化和维护

索引设计的建议（针对B树索引）

1）选择可选性高的字段建立索引。
2）对于多表连接，选择被驱动表的连接字段建立索引
3）如果选择字段有多个但可选性都不高，建议创建复合索引，复合索引的首个字段应选择使用率最高的字段

为了避免索引被抑制，SQL语句中应避免以下情况：

1）字段类型不匹配
2）条件语句中使用了函数（包括自定义函数）
3）条件语句中使用了公式

函数索引可针对上面第2）条情况，但因为DML操作对维护函数索引的成本较高，应慎重使用。

---

与索引有关的等待事件主要是db file sequential read，原因可能是

1）应用设计好，索引使用率高（正常现象）。
2）索引创建得过多，DML操作带来索引维护的开销。
3）索引碎片过多。
4）索引设计不好，可选度低，近似于全表扫描。

---

监控索引是否被使用

多余的索引会增加维护开销，因此有必要确认是否存在多余索引。

点击(此处)折叠或打开

1. alter index price_pk_id monitoring usage;
   
2. alter index bom_pk_id monitoring usage;

查看索引是否被使用

点击(此处)折叠或打开

1. select * from v$object_usage;

INDEX_NAME   TABLE_NA MONITO USED   START_MONITORING
------------ -------- ------ ------ -------------------------
END_MONITORING
-------------------------
PRICE_PK_ID  PRICE    YES    NO     10/22/2014 11:59:51




BOM_PK_ID    BOM      YES    YES    10/22/2014 12:03:51

最后关闭索引监控，

点击(此处)折叠或打开

1. alter index price_pk_id nomonitoring usage;
   
2. alter index bom_pk_id nomonitoring usage;

---

查看索引碎片

大量索引碎片会降低索引性能，查看索引碎片：

点击(此处)折叠或打开

1. analyze index price_pk_id validate structure offline;

查看结果

点击(此处)折叠或打开

1. select name,del_lf_rows_len,lf_rows_len
   
2. ,(del_lf_rows_len/lf_rows_len)*100 as rate
   
3. from index_stats;

NAME            DEL_LF_ROWS_LEN LF_ROWS_LEN       RATE
--------------- --------------- ----------- ----------
PRICE_PK_ID                5459     5480620 .099605519

当索引碎片超过20%，表示已经非常严重，应选择重建索引。

而此时，索引的集群因子应该也非常大，查看：

点击(此处)折叠或打开

1. SELECT i.index_name, i.CLUSTERING_FACTOR FROM dba_indexes i WHERE i.index_name like ’PK_TESTCF%’;

重建索引：

点击(此处)折叠或打开

1. alter index price_pk_id rebuild;

重建后再分析索引，碎片就不存在了。

---

2015.4.23更新

对于已经重建的索引，立即查询集群因子，可能并没有看到有什么变化：

点击(此处)折叠或打开

1. col index_name format a15
   
2. col index_type format a10
   
3. col num_rows format 999,999,999
   
4. col LEAF_BLOCKS format 999,999,999
   
5. col CLUSTERING_FACTOR format 999,999,999
   
6. 
   
7. SELECT INDEX_NAME, INDEX_TYPE, NUM_ROWS, LEAF_BLOCKS, CLUSTERING_FACTOR
   
8. FROM DBA_INDEXES
   
9. WHERE INDEX_NAME='IOB';

INDEX_NAME      INDEX_TYPE     NUM_ROWS  LEAF_BLOCKS CLUSTERING_FACTOR
--------------- ---------- ------------ ------------ -----------------
IOB             NORMAL                0            0                 0

需要在进行索引分析后，才能看到索引统计信息的变化：

点击(此处)折叠或打开

1. analyze index HK.IOB validate structure offline;
   
2. 
   
3. col index_name format a15
   
4. col index_type format a10
   
5. col num_rows format 999,999,999
   
6. col LEAF_BLOCKS format 999,999,999
   
7. col CLUSTERING_FACTOR format 999,999,999
   
8. 
   
9. SELECT INDEX_NAME, INDEX_TYPE, NUM_ROWS, LEAF_BLOCKS, CLUSTERING_FACTOR
   
10. FROM DBA_INDEXES
    
11. WHERE INDEX_NAME='IOB';

INDEX_NAME      INDEX_TYPE     NUM_ROWS  LEAF_BLOCKS CLUSTERING_FACTOR
--------------- ---------- ------------ ------------ -----------------
IOB             NORMAL            7,217           17               126

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22621861/viewspace-1307169/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22621861/viewspace-1307169/