Oracle index 使用的一个总结

1. 什么情况需要建index
oracle中的很多表建索引是根据业务需求建的，比如：主键，常用的columns。 oracle提出了一个建index 的理论依据就是可选择性(selectivity)：

比较一下列中唯一数量的个数和表中的行数，就可以判断该列的可选择性。
如果该列的”唯一键的数量/表中的行数”的比值越接近1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。
在可选择性高的列上进行查询时，返回的数据就较少，比较适合使用索引查询。

2. 数据的理想路径  ROWID--> 数据内容；
一般的是  index-->ROWID---> 数据内容；

数据内容如果跨多个DB Block，就是行连接
rowid指的不是数据内容，而是一个指针，就是行迁移。  如果多了的话，需要处理碎片。

3. 索引的扫描分类

索引唯一扫描(index unique scan)   --- where 条件之后，返回一行记录
索引范围扫描(index range scan)    --- 多行记录
索引全扫描(index full scan)       --- 排序
索引快速扫描(index fast full scan) -- 不排序

4. oracle 认为，选择的结果集超过全部记录的5%，FTS就比index 快。

5. 位图index，只有类似男，女； 北京，上海，广州，之类的可以list出来的column 值才可以考虑建bitmap index

6. For example：

B_Tree index

CREATE INDEX hr.employees_last_name_idx
ON hr.employees(last_name)
PCTFREE 30
STORAGE(INITIAL 200K NEXT 200K
    PCTINCREASE 0     MAXEXTENTS 50)
TABLESPACE indx;

Bitmap Index

CREATE BITMAP INDEX orders_region_id_idx
ON orders(region_id)
PCTFREE 30
STORAGE(INITIAL 200K NEXT 200K
    PCTINCREASE 0     MAXEXTENTS 50)
TABLESPACE indx;

7. Rebuild index

主要还是看index的扁平程度,
Rebuild the index when :
  - deleted entries represent 20% or more of the current entries
  - the index depth is more then 4 levels.
Possible candidate for bitmap index :
  - when distinctiveness is more than 99%
 
条件1
在分析(analyze)指定索引之后，查询index_stats的height字段的值，如果这个值>=4,则最好重建（rebuild）这个索引。
虽然这个规则不是总是正确，但如果这个值一直都是不变的，则这个索引也就不需重建。

条件2
在分析(analyze)指定索引之后，查询index_stats的del_lf_rows和lf_rows的值,如果(del_lf_rows/lf_rows)*100 > = 20，则这个索引也需要重建。
例子：
SQL > analyze index IND_PK validate structure;
 
SQL > select name,height,del_lf_rows,lf_rows,(del_lf_rows/lf_rows) *100 from index_stats;
 
NAME                               HEIGHT DEL_LF_ROWS    LF_ROWS (DEL_LF_ROWS/LF_ROWS)*100
 
------------------------------ ---------- ----------- ---------- -------------------------
 
INDX_PK                                4      277353     990206                28.0096263
SQL> alter index IND_PK rebuild;

ALTER INDEX orders_region_id_idx REBUILD TABLESPACE indx02;
ALTER INDEX orders_id_idx REBUILD ONLINE;

8. 监事index 的使用

SQL> alter index pk_t_2 monitoring usage;
SQL> alter index pk_t_2 nomonitoring usage;
SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE START_MONI
TORING    END_MONITORING
------------------------------ ------------------------------ --- --- ----------
--------- -------------------
PK_T_2                         TES_P                          NO  YES 11/24/2015
 13:40:34 11/24/2015 13:41:24

如果长时间没有使用的index，可以和应用确认，是否可以删除。


 
 




















 


 

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

转载于:http://blog.itpub.net/6126/viewspace-1845479/