索引的管理
在昨天学习完了索引的基本用法之后,今天继续学习一下对索引的管理。基本上就是一些日常的操作语法,以及数据字典的掌握。记下来也可以在今后用来查询。另外,本文的最后几章,关于索引的用法,在平常的使用中是非常有用的,一定要仔细掌握!!!下面开始:
合并与重建索引:
随着对表的不断更新,表中的索引会产生越来越多的存储碎片,要对碎片进行整理有两种方式:重建索引或合并索引。
合并索引:将B树索引中的叶节点存储碎片进行合并。
ALTER INDEX ind_t COALESCE deallocate unused;
注:不加deallocate unused则合并后仍保留多于的空间。
重建索引:即进行重新建立,而且可以修改之前的各种参数。
ALTER INDEX ind_t REBUILD
tablespace users
storage(initial 128k
next 64k);
注:在改变属性时还可以修改REVERSE参数,改为反向,或反向改为正向。
对于分区表,一次只能重建其中的一个分区。
ALTER INDEX ind_t rebuild
partition p1;
监视索引:
监视索引的使用情况:
打开:ALTERINDEX INDEX_T5 MONITORINGUSAGE;
查看:select * from v$object_usage;
关闭:ALTERINDEX INDEX_T5 NOMONITORINGUSAGE;
监视索引空间使用:
分析:ANALYZEINDEX INDEX_T5 VALIDATESTRUCTURE;
查看:select br_rows,br_blks,lf_rows,del_lf_rows from index_stats;
说明:BR_ROWS为B树分支数,LF_ROWS为B树叶节点数,DEL_LF_ROWS删除但保存在结构中的节点数。
注:当30%的叶节点是已经删除的叶节点时,则应该考虑重建和合并索引。
删除索引:
当一下情况时需要删除索引:
1、不再需要的索引;
2、通过一段时间观察,发现很少使用该索引;
3、该索引无效,必须在重建之前删除;
4、该索引包含过多存储碎片,需要在重建之前删除该索引;
5、索引没有提供所期望的性能改善;
虽然索引的建立没有限制,但是也不是说建立的越多越好,建立所以需要一下的代价:
1、基础表维护时,系统要同时维护索引;
2、插入、更新、删除数据时需要同时修改索引;
3、在更改数据时会产生大量db file sequential read锁等待;
DROP INDEX ind_t;
索引使用的限制情况:
限制索引是一些没有经验的开发人员经常犯的错误之一。在SQL中有很多陷阱会使一些索引无法使用。具体有如下情况:
1、
使用不等于操作符(<>、!=)
下面的查询即使在cust_rating列有一个索引,查询语句仍然执行一次全表扫描。
select cust_Id,cust_name
from customers
where cust_rating <> 'aa';
把上面的语句改成如下的查询语句,在采用基于规则的优化器而不是基于代价的优化器时,将会使用索引。
select cust_Id,cust_name
from customers
where cust_rating < 'aa' or cust_rating > 'aa';
注:通过把不等于操作符改成OR条件,就可以使用索引,以避免全表扫描。
2、使用IS NULL 或IS NOT NULL
使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。
在SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时,把需要索引的列设成NOT NULL。
如果被索引的列在某些行中存在NULL值,就不会使用这个索引,除非索引是一个位图索引。
3、使用函数
如果不使用基于函数的索引,那么在SQL语句的WHERE子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。
select empno,ename,deptno
from emp
where trunc(hiredate)='01-MAY-81';
把上面的语句改成下面的语句,这样就可以通过索引进行查找。
select empno,ename,deptno
from emp
where hiredate<(to_date('01-MAY-81')+0.9999);
使用这个方法也可以屏蔽不想使用的不良索引,例如:
数值型:在索引字段上加0,例如
select * from t1 where empno+0 = v_empno;
字符型:在索引字段上加'',例如
select * from t1 where type||'' = v_type;
4、比较不匹配的数据类型
比较不匹配的数据类型也是比较难于发现的性能问题之一。
注意下面查询的例子,account_number是一个VARCHAR2类型,在account_number字段上有索引。下面的语句将执行全表扫描。
select bank_name,address,city,state,zip
from banks
where account_number = 990354;
Oracle可以自动把where子句变成to_number(account_number)=990354,这样就限制了索引的使用,改成下面的查询就可以使用索引:
select bank_name,address,city,state,zip
from banks
where account_number ='990354';
注:不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。
5、当使系统数据字典或视图
系统的数据字典表都未被分析过,可能导致极差的“执行计划”。但是不要擅自对数据字典表做分析,否则可能导致死锁,或系统性能下降。
创建索引的规则:
1、经常检索排序大表中40%或非排序表7%的行,建议建索引;
2、为了改善多表关联,索引列用于联结;
3、列中的值相对比较唯一;
4、
取值范围(大:B树索引,小:位图索引);
5、Date型列一般适合基于函数的索引;
6、列中有许多空值,不适合建立索引;
7、建议每张表不超过5个索引;
8、经常一起使用多个字段检索记录,组合索引比单索引更有效,把最常用的列放在最前面;
9、合理设定pctfress,注意:不能给索引指定pctused;