Oracle优化经典文章------索引原理篇

原创 2007年09月19日 15:00:00
Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁,并导致数据库性能急剧下降或进程终止。而如果做出正确的选择,则可以合理使用资源,使那些已经运行了几个小时甚至几天的进程在几分钟得以完成,这样会使您立刻成为一位英雄。这篇文章就将简单的讨论每个索引选项。主要有以下内容:

[1] 基本的索引概念
    查询DBA_INDEXES视图可得到表中所有索引的列表,注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。访问USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。

[2] 组合索引
    当某个索引包含有多个已索引的列时,称这个索引为组合(concatented)索引。在 Oracle9i引入跳跃式扫描的索引访问方法之前,查询只能在有限条件下使用该索引。比如:表emp有一个组合索引键,该索引包含了empno、ename和deptno。在Oracle9i之前除非在where之句中对第一列(empno)指定一个值,否则就不能使用这个索引键进行一次范围扫描。
    特别注意:在Oracle9i之前,只有在使用到索引的前导索引时才可以使用组合索引!

[3] ORACLE ROWID
    通过每个行的ROWID,索引Oracle提供了访问单行数据的能力。ROWID其实就是直接指向单独行的线路图。如果想检查重复值或是其他对ROWID本身的引用,可以在任何表中使用和指定rowid列。   

[4] 限制索引
    限制索引是一些没有经验的开发人员经常犯的错误之一。在SQL中有很多陷阱会使一些索引无法使用。下面讨论一些常见的问题:
   
    4.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条件,就可以使用索引,以避免全表扫描。
     
     4.2 使用IS NULL 或IS NOT NULL
         使用IS NULL 或IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。因此建议开     发人员在建表时,把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值,就不会使用这个索引(除非索引是一个位图索     引,关于位图索引在稍后在详细讨论)。

     4.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);

     4.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] 选择性
    使用USER_INDEXES视图,该视图中显示了一个distinct_keys列。比较一下唯一键的数量和表中的行数,就可以判断索引的选择性。选择性越高,索引返回的数据就越少。

[6] 群集因子(Clustering Factor)
    Clustering Factor位于USER_INDEXES视图中。该列反映了数据相对于已索引的列是否显得有序。如果Clustering Factor列的值接近于索引中的树叶块(leaf block)的数目,表中的数据就越有序。如果它的值接近于表中的行数,则表中的数据就不是很有序。
   

[7] 二元高度(Binary height)
    索引的二元高度对把ROWID返回给用户进程时所要求的I/O量起到关键作用。在对一个索引进行分析后,可以通过查询DBA_INDEXES的B-level列查看它的二元高度。二元高度主要随着表的大小以及被索引的列中值的范围的狭窄程度而变化。索引上如果有大量被删除的行,它的二元高度也会增加。更新索引列也类似于删除操作,因为它增加了已删除键的数目。重建索引可能会降低二元高度。

[8] 快速全局扫描
    在Oracle7.3后就可以使用快速全局扫描(Fast Full Scan)这个选项。这个选项允许Oracle执行一个全局索引扫描操作。快速全局扫描读取B-树索引上所有树叶块。初始化文件中的DB_FILE_MULTIBLOCK_READ_COUNT参数可以控制同时被读取的块的数目。

[9] 跳跃式扫描
    从Oracle9i开始,索引跳跃式扫描特性可以允许优化器使用组合索引,即便索引的前导列没有出现在WHERE子句中。索引跳跃式扫描比全索引扫描要快的多。下面的程序清单显示出性能的差别:
    create index skip1 on emp5(job,empno);
    index created.

    select count(*)
    from emp5
    where empno=7900;

    Elapsed:00:00:03.13

    Execution Plan
    0     SELECT STATEMENT Optimizer=CHOOSE(Cost=4 Card=1 Bytes=5)
    1  0    SORT(AGGREGATE)
    2  1      INDEX(FAST FULL SCAN) OF 'SKIP1'(NON-UNIQUE)

    Statistics

    6826 consistent gets
    6819 physical   reads

    select /*+ index(emp5 skip1)*/ count(*)
    from emp5
    where empno=7900;

    Elapsed:00:00:00.56

    Execution Plan
    0     SELECT STATEMENT Optimizer=CHOOSE(Cost=6 Card=1 Bytes=5)
    1  0    SORT(AGGREGATE)
    2  1      INDEX(SKIP SCAN) OF 'SKIP1'(NON-UNIQUE)

    Statistics

    21 consistent gets
    17 physical   reads

[10] 索引的类型
     B-树索引
     位图索引
     HASH索引
     索引编排表
     反转键索引
     基于函数的索引
     分区索引
     本地和全局索引
     特别说明:关于这部分内容大家感兴趣可以自己查找资料学习!
 

Oracle优化经典文章------索引原理篇

Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁,并导致数据库性能急剧下降或进程终止。而如果做出正确的选择,则可以合理使用资...
  • redphoenix
  • redphoenix
  • 2007年02月06日 17:20
  • 398

Oracle优化经典文章------索引原理篇(ZT)

 Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁,并导致数据库性能急剧下降或进程终止。而如果做出正确的选择,则可以合理使用...
  • xyz846
  • xyz846
  • 2011年04月16日 17:28
  • 284

Oracle优化----索引原理篇

Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁,并导致数据库性能急剧下降或进程终止。而如果做出正确的选择,则可以合理使用资...
  • 2i3i4i
  • 2i3i4i
  • 2006年04月04日 15:09
  • 1907

oracle优化- 索引

如果发现Oracle在有索引的情况下而没有使用索引,这并不是Oracle的优化器出错。在有些情况下Oracle确实会选择全表扫描(Full Table Scan),而非索引扫描(Index Scan)...
  • fjnucse
  • fjnucse
  • 2008年06月19日 14:32
  • 622

一篇比较精辟的索引文章

实际上,您可以把索引理解为一种特殊的目录。微软的SQL   SERVER提供了两种索引:聚集索引(clustered   index,也称聚类索引、簇集索引)和非聚集索引(nonclustered  ...
  • wxw_317
  • wxw_317
  • 2012年08月21日 11:02
  • 536

Oracle数据库经典优化之索引原理篇

 Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁,并导致数据库性能急剧下降或进程终止。而如果做出正确的选择,则可以合理使用...
  • mmquit
  • mmquit
  • 2008年01月31日 15:32
  • 731

文章索引目录

投稿文章 【FreeBuf.COM】DHT爬虫:18.4GB种子分析小记 【FreeBuf.COM】技术分享:如何扫描统计全国Telnet默认口令 Python 统计某招聘网岗位职责要求关键字权...
  • zrools
  • zrools
  • 2016年04月11日 15:46
  • 324

李承鹏写给CNN的一篇巨牛的文章。-----相当经典哈。。。

我的美国同行CNN及卡弗蒂你们好: 春天来了,听说我的美国同行不小心患了口蹄疫,十分着急,贵国的性病一直是长在下半身,不知为何这次集体发作于口腔,望告知详情,切切。 我的英语一直不好,但从来没有把CN...
  • Gaon_Aaron
  • Gaon_Aaron
  • 2008年04月20日 09:15
  • 1368

了解三层结构经典的文章!!!

看了大家的讨论,发觉很多人对数据库三层结构的概念还不很清楚,或者很想深入了解其中的奥秘。为了满足大家的需求,我贴个帖子,希望对大家有所帮助。不过实现三层结构的技术实在太多(MTS,CORBA,COM,...
  • regedit362451dennis
  • regedit362451dennis
  • 2005年08月19日 10:24
  • 1091

Oracle优化--索引原理

Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁,并导致数据库性能急剧下降或进程终止。而如果做出正确的选择,则可以合理使用资...
  • qq_30114557
  • qq_30114557
  • 2017年04月06日 15:56
  • 75
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle优化经典文章------索引原理篇
举报原因:
原因补充:

(最多只允许输入30个字)