Decode360's Blog

业精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com

  BlogJava :: 首页 :: 新随笔 :: 联系 ::  :: 管理 ::
  397 随笔 :: 33 文章 :: 29 评论 :: 0 Trackbacks
索引的管理
 
    在昨天学习完了索引的基本用法之后,今天继续学习一下对索引的管理。基本上就是一些日常的操作语法,以及数据字典的掌握。记下来也可以在今后用来查询。另外,本文的最后几章,关于索引的用法,在平常的使用中是非常有用的,一定要仔细掌握!!!下面开始:
 
 
合并与重建索引:
 
    随着对表的不断更新,表中的索引会产生越来越多的存储碎片,要对碎片进行整理有两种方式:重建索引合并索引
 
    合并索引:将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;
 
 
 
 
posted on 2008-10-12 22:16 decode360 阅读(165) 评论(0)  编辑  收藏 所属分类: 07.Oracle

只有注册用户登录后才能发表评论。


网站导航: