精华内容
下载资源
问答
  • Oracle索引原理

    千次阅读 2017-10-23 17:17:18
    索引原理 oracle中的索引与mysql中的索引不一样,oracle中索引是存储了索引列的值以及rowid值。而mysql索引分为聚集索引,非聚集索引。其中聚集索引只能有一个。 oracle中索引的主要分为根,茎,叶子三部分。索引列...

    索引原理

    1. oracel中索引类型很多,默认采用B树索引。
    2. oracle中的索引与mysql中的索引不一样,oracle中索引是存储了索引列的值以及rowid值。而mysql索引分为聚集索引,非聚集索引。其中聚集索引只能有一个。
    3. oracle中索引的主要分为根,茎,叶子三部分。索引列值都是存放在叶子节点上,茎只是存放了叶子节点的相关信息。
    4. oracle中的索引反应的是逻辑结构,不是物理结构。索引创建的时候,是先创建叶子节点,然后再创建茎,最后创建根。从索引的创建过程分析可以看出来。
    5. oracle中数据单位是以块为单位的,数据存储满一个数据块block,再去存储下一个数据块block。

    索引特点

    1. 索引高度一般比较低
    2. 索引是按照有序排列的。
    3. 索引存储列值及rowid值

    如何建索引

    1. oracle会在主键上创建索引
    2. oracle会在非空唯一索引列上创建索引。
    3. 经常出现的谓词,即出现在执行频率很高的SQL查询的where条件中的列上。
    4. 索引最好有单独的表空间,有利于备份和维护

    索引缺点

    1. 索引需要占据存储空间,索引能提供查询速度,但是维护和更新却浪费空间。因为索引是有序排列的。

    影响性能缘由

    1. oracle中sql语句,在查询时影响性能主要有逻辑读,排序,花费时间time代价cost(cpu)全表扫描等多个因素。

    索引应用

    1. order by
      1.1 order by 列名。会对order by 后面的列进行排序,
    2. distinct
      2.1 distinct 会进行排序

    索引压缩

    1. 在索引列中(一列或者多列),如果有多行数值相同,为了提供查询数据速度,可以将索引进行压缩存储。索引最终也是要存储在数据块上,压缩索引是为了减少扫描数据块的个数。
    2. 可以采用compress N子句方式创建压缩索引。
    3. create index index_name on tableName(col1,col2) compress N。
      3.1 如果N为1,则会对第一列进行压缩存储,N为2,则会对第一列和第二列压缩存储。

    组合索引设计

    1. 适当场合尽量避免回表读
    2. 组合索引中的列数越少越高效
    3. 组合索引中有2列时,情况又不一样:
      3.1 组合索引中,等值查询时,无论哪列在前在后都无所谓。
      3.2 组合索引的2列中,如果一列是范围查询,一列是等值查询,则一定要把等值查询放在前面,范围查询列放在后面才能使索引高效。这里是创建索引的时候,等值查询列在组合索引的最前面,范围列在最后面,不是sql语句中的书写顺序。
    4. 如果单列的查询与组合索引的前置列查询一样,则单列可以不用创建索引,直接利用组合索引来检索数据。最好将该列放在组合索引的第一列
    5. 经常一起出现在where字句中的列可以创建索引

    函数索引

    1. 在SQL查询中,如果where条件中的列上创建了索引,但是在该索引列上使用了函数,则oracle函数会使当前列上的索引失效。
    2. 对于上述情况,需要创建函数索引。
    3. 创建函数索引需要在创建语句中指定使用的函数和列。create index index_name on table_name(函数(列名))
    4. 如果创建了函数索引,则不能直接修改。需要先删除索引,再修改列,然后重建。

    不可见索引

    1. 有时候想增加一个索引而又不影响线上应用的性能,即通知SQL优化器默认不使用该索引。需要在创建索引的时候,设置为不可见。
    2. create index index_Name on table_name(col1,col2) invisible。
    3. 创建完索引后,需要确保optimizer_use_invisible_indexes的值为true。可以使用alter system set optimizer_use_invisible_indexes= true
    4. 使用hit提示,可以显示通知优化器使用该索引。

    查看表中索引方式

    1. 可以在表user_indexes中查看索引方式 。
    展开全文
  • Oracle 索引原理

    2020-06-17 15:11:05
    B-TREE索引 一个B树索引只有一个根节点,它实际就是位于树的最顶端的分支节点。 可以用下图一来描述B树索引的结构。其中,B表示分支节点,而L表示叶子节点。 对于分支节点块(包括根节点块)来说,其所包含的...

    B-Tree索引

    一个B树索引只有一个根节点,它实际就是位于树的最顶端的分支节点。

    可以用下图一来描述B树索引的结构。其中,B表示分支节点,而L表示叶子节点。



        对于分支节点块(包括根节点块)来说,其所包含的索引条目都是按照顺序排列的(缺省是升序排列,也可以在创建索引时指定为降序排列)。每个索引条目(也可以叫做每条记录)都具有两个字段。第一个字段表示当前该分支节点块下面所链接的索引块中所包含的最小键值;第二个字段为四个字节,表示所链接的索引块的地址,该地址指向下面一个索引块。在一个分支节点块中所能容纳的记录行数由数据块大小以及索引键值的长度决定。比如从上图一可以看到,对于根节点块来说,包含三条记录,分别为(0 B1)、(500 B2)、(1000 B3),它们指向三个分支节点块。其中的0、500和1000分别表示这三个分支节点块所链接的键值的最小值。而B1、B2和B3则表示所指向的三个分支节点块的地址。

     

        对于叶子节点块来说,其所包含的索引条目与分支节点一样,都是按照顺序排列的(缺省是升序排列,也可以在创建索引时指定为降序排列)。每个索引条目(也可以叫做每条记录)也具有两个字段。第一个字段表示索引的键值,对于单列索引来说是一个值;而对于多列索引来说则是多个值组合在一起的。第二个字段表示键值所对应的记录行的ROWID,该ROWID是记录行在表里的物理地址。如果索引是创建在非分区表上或者索引是分区表上的本地索引的话,则该ROWID占用6个字节;如果索引是创建在分区表上的全局索引的话,则该ROWID占用10个字节。

    bitmap索引

          位图(bitmap)索引是另外一种索引类型,它的组织形式与B树索引相同,也是一棵平衡树。与B树索引的区别在于叶子节点里存放索引条目的方式不同。从前面我们知道,B树索引的叶子节点里,对于表里的每个数据行,如果被索引列的值不为空的,则会为该记录行在叶子节点里维护一个对应的索引条目。
    而位图索引则不是这样,其叶子节点里存放的索引条目如下图所示。

        假设某个表T里所有的记录在列C1上只具有三个值:01、02和03。在表T的C1列上创建位图索引以后,则叶子节点的内容如图9-14所示。可以看到,位图索引只有三个索引条目,也就是每个C1列的值对应一个索引条目。位图索引条目上还包含表里第一条记录所对应的ROWID以及最后一条记录所对应的ROWID。索引条目的最后一部分则是由多个bit位所组成的bitmap,每个bit位就对应一条记录。

           当发出where c1='01'这样的SQL语句时,oracle会去搜索01所在的索引条目,然后扫描该索引条目中的bitmap里所有的bit位。第一个bit位为1,则说明第一条记录上的C1值为01,于是返回第一条记录所在的ROWID(根据该索引条目里记录的start ROWID加上行号得到该记录所在的ROWID)。第二个bit位为0,则说明第二条记录上的C1值不为01,依此类推。另外,如果索引列为空,也会在位图索引里记录,也就是将对应的bit位设置为0即可。
           如果索引列上不同值的个数比较少的时候,比如对于性别列(男或女)等,则使用位图索引会比较好,因为它对空间的占用非常少(因为都是用bit位来表示表里的数据行),从而在扫描索引的时候,扫描的索引块的个数也比较少。可以试想一下,如果在列的不同值非常多的列上,比如主键列上,创建位图索引,则产生的索引条目就等于表里记录的条数,同时每个索引条目里的bitmap里,只有一个1,其它都是0。这样还不如B树索引的效率高。
    如果被索引的列经常被更新的话,则不适合使用位图索引。因为当更新位图所在的列时,由于要在不同的索引条目之间修改bit位,比如将第一条记录从01变为02,则必须将01所在的索引条目的第一个bit位改为0,再将02所在的索引条目的第一个bit位改为1。因此,在更新索引条目的过程中,会锁定位图索引里多个索引条目。也就是同时只能有一个用户能够更新表T,从而降低了并发性。
    位图索引比较适合用在数据仓库系统里,不适合用在OLTP系统里。

     HASH索引

          使用HASH索引必须要使用HASH集群。建立一个集群或HASH集群的同时,也就定义了一个集群键。这个键告诉Oracle如何在集群上存储表。在存储数据时,所有与这个集群键相关的行都被存储在一个数据库块上。如果数据都存储在同一个数据库块上,并且将HASH索引作为WHERE子句中的确切匹配,Oracle就可以通过执行一个HASH函数和I/O来访问数据-- 而通过使用一个二元高度为4的B树索引来访问数据,则需要在检索数据时使用4个I/O。如图2-5所示,其中的查询是一个等价查询,用于匹配HASH列和确切的值。Oracle可以快速使用该值,基于HASH函数确定行的物理存储位置。

          HASH索引可能是访问数据库中数据的最快方法,但它也有自身的缺点。集群键上不同值的数目必须在创建HASH集群之前就要知道。需要在创建HASH集群的时候指定这个值。低估了集群键的不同值的数字可能会造成集群的冲突(两个集群的键值拥有相同的HASH值)。这种冲突是非常消耗资源的。冲突会造成用来存储额外行的缓冲溢出,然后造成额外的I/O。如果不同HASH值的数目已经被低估,您就必须在重建这个集群之后改变这个值。ALTER CLUSTER命令不能改变HASH键的数目。

          HASH集群还可能浪费空间。如果无法确定需要多少空间来维护某个集群键上的所有行,就可能造成空间的浪费。如果不能为集群的未来增长分配好附加的空间,HASH集群可能就不是最好的选择。

    如果应用程序经常在集群表上进行全表扫描,HASH集群可能也不是最好的选择。由于需要为未来的增长分配好集群的剩余空间量,全表扫描可能非常消耗资源。

     

    在实现HASH集群之前一定要小心。您需要全面地观察应用程序,保证在实现这个选项之前已经了解关于表和数据的大量信息。通常,HASH对于一些包含有序值的静态数据非常有效。

    技巧:

    HASH索引在有限制条件(需要指定一个确定的值而不是一个值范围)的情况下非常有用。

    聚族索引

          在这里还是用字典来进行类比,一般来说汉语字典中有几种索引,如拼音、偏旁、笔画等。字典本身的组织也是排序的,我记得一般是按照拼音排序的。这里的拼音就是聚族索引。也就是说聚族索引的组织顺序和数据本身的组织顺序是一致的 ,这也解释了数据库中只能定义一个聚族索引的原因,因为数据本身只能按一种方式进行排序。
          那聚族索引有什么特别的好处呢,这个好处就是在数据库中执行查找一批数据的语句会比较快,因为数据已经按照聚族索引排好序了,很少的io操作就可以将数据从库中取出。好比你在字典中查找发音从从a到c的汉字,只需要查到a的开始页和c的结束页,中间的所有页都符合查询要求,不用再一页一页地查找。

     

     非聚族索引

          非聚族索引就好比字典里的偏旁、笔画索引, 索引组织顺序和数据组织顺序不一致 ,因此非聚族索引可以创建多个。当查找一条数据时,非聚族索引和聚族索引的效率相差不大,但查找一批数据(n)时,非聚族索引需要的io可能是聚族索引的n倍,因为非聚族索引需要一条一条地进行查找。

     

    转载于:https://www.iteye.com/blog/orange5458-1165319 

    展开全文
  • ORACLE 索引原理

    2019-09-14 16:15:46
    B-TREE索引 一个B树索引只有一个根节点,它实际就是位于树的最顶端的分支节点。 可以用下图一来描述B树索引的结构。其中,B表示分支节点,而L表示叶子节点。 对于分支节点块(包括根节点块)来说,其所包含...

    (本文内容均整理自万能的INTERNET)

    • B-TREE索引

    一个B树索引只有一个根节点,它实际就是位于树的最顶端的分支节点。

    可以用下图一来描述B树索引的结构。其中,B表示分支节点,而L表示叶子节点。



        对于分支节点块(包括根节点块)来说,其所包含的索引条目都是按照顺序排列的(缺省是升序排列,也可以在创建索引时指定为降序排列)。每个索引条目(也可以叫做每条记录)都具有两个字段。第一个字段表示当前该分支节点块下面所链接的索引块中所包含的最小键值;第二个字段为四个字节,表示所链接的索引块的地址,该地址指向下面一个索引块。在一个分支节点块中所能容纳的记录行数由数据块大小以及索引键值的长度决定。比如从上图一可以看到,对于根节点块来说,包含三条记录,分别为(0 B1)、(500 B2)、(1000 B3),它们指向三个分支节点块。其中的0、500和1000分别表示这三个分支节点块所链接的键值的最小值。而B1、B2和B3则表示所指向的三个分支节点块的地址。

        对于叶子节点块来说,其所包含的索引条目与分支节点一样,都是按照顺序排列的(缺省是升序排列,也可以在创建索引时指定为降序排列)。每个索引条目(也可以叫做每条记录)也具有两个字段。第一个字段表示索引的键值,对于单列索引来说是一个值;而对于多列索引来说则是多个值组合在一起的。第二个字段表示键值所对应的记录行的ROWID,该ROWID是记录行在表里的物理地址。如果索引是创建在非分区表上或者索引是分区表上的本地索引的话,则该ROWID占用6个字节;如果索引是创建在分区表上的全局索引的话,则该ROWID占用10个字节。

     

    • bitmap索引

          位图(bitmap)索引是另外一种索引类型,它的组织形式与B树索引相同,也是一棵平衡树。与B树索引的区别在于叶子节点里存放索引条目的方式不同。从前面我们知道,B树索引的叶子节点里,对于表里的每个数据行,如果被索引列的值不为空的,则会为该记录行在叶子节点里维护一个对应的索引条目。
    而位图索引则不是这样,其叶子节点里存放的索引条目如下图所示。

        假设某个表T里所有的记录在列C1上只具有三个值:01、02和03。在表T的C1列上创建位图索引以后,则叶子节点的内容如图9-14所示。可以看到,位图索引只有三个索引条目,也就是每个C1列的值对应一个索引条目。位图索引条目上还包含表里第一条记录所对应的ROWID以及最后一条记录所对应的ROWID。索引条目的最后一部分则是由多个bit位所组成的bitmap,每个bit位就对应一条记录。

           当发出where c1='01'这样的SQL语句时,oracle会去搜索01所在的索引条目,然后扫描该索引条目中的bitmap里所有的bit位。第一个bit位为1,则说明第一条记录上的C1值为01,于是返回第一条记录所在的ROWID(根据该索引条目里记录的start ROWID加上行号得到该记录所在的ROWID)。第二个bit位为0,则说明第二条记录上的C1值不为01,依此类推。另外,如果索引列为空,也会在位图索引里记录,也就是将对应的bit位设置为0即可。
           如果索引列上不同值的个数比较少的时候,比如对于性别列(男或女)等,则使用位图索引会比较好,因为它对空间的占用非常少(因为都是用bit位来表示表里的数据行),从而在扫描索引的时候,扫描的索引块的个数也比较少。可以试想一下,如果在列的不同值非常多的列上,比如主键列上,创建位图索引,则产生的索引条目就等于表里记录的条数,同时每个索引条目里的bitmap里,只有一个1,其它都是0。这样还不如B树索引的效率高。
    如果被索引的列经常被更新的话,则不适合使用位图索引。因为当更新位图所在的列时,由于要在不同的索引条目之间修改bit位,比如将第一条记录从01变为02,则必须将01所在的索引条目的第一个bit位改为0,再将02所在的索引条目的第一个bit位改为1。因此,在更新索引条目的过程中,会锁定位图索引里多个索引条目。也就是同时只能有一个用户能够更新表T,从而降低了并发性。
    位图索引比较适合用在数据仓库系统里,不适合用在OLTP系统里。

     

    • HASH索引

          使用HASH索引必须要使用HASH集群。建立一个集群或HASH集群的同时,也就定义了一个集群键。这个键告诉Oracle如何在集群上存储表。在存储数据时,所有与这个集群键相关的行都被存储在一个数据库块上。如果数据都存储在同一个数据库块上,并且将HASH索引作为WHERE子句中的确切匹配,Oracle就可以通过执行一个HASH函数和I/O来访问数据-- 而通过使用一个二元高度为4的B树索引来访问数据,则需要在检索数据时使用4个I/O。如图2-5所示,其中的查询是一个等价查询,用于匹配HASH列和确切的值。Oracle可以快速使用该值,基于HASH函数确定行的物理存储位置。

          HASH索引可能是访问数据库中数据的最快方法,但它也有自身的缺点。集群键上不同值的数目必须在创建HASH集群之前就要知道。需要在创建HASH集群的时候指定这个值。低估了集群键的不同值的数字可能会造成集群的冲突(两个集群的键值拥有相同的HASH值)。这种冲突是非常消耗资源的。冲突会造成用来存储额外行的缓冲溢出,然后造成额外的I/O。如果不同HASH值的数目已经被低估,您就必须在重建这个集群之后改变这个值。ALTER CLUSTER命令不能改变HASH键的数目。

          HASH集群还可能浪费空间。如果无法确定需要多少空间来维护某个集群键上的所有行,就可能造成空间的浪费。如果不能为集群的未来增长分配好附加的空间,HASH集群可能就不是最好的选择。

    如果应用程序经常在集群表上进行全表扫描,HASH集群可能也不是最好的选择。由于需要为未来的增长分配好集群的剩余空间量,全表扫描可能非常消耗资源。

     

    在实现HASH集群之前一定要小心。您需要全面地观察应用程序,保证在实现这个选项之前已经了解关于表和数据的大量信息。通常,HASH对于一些包含有序值的静态数据非常有效。

    技巧:

    HASH索引在有限制条件(需要指定一个确定的值而不是一个值范围)的情况下非常有用。

     

    • 聚族索引

          在这里还是用字典来进行类比,一般来说汉语字典中有几种索引,如拼音、偏旁、笔画等。字典本身的组织也是排序的,我记得一般是按照拼音排序的。这里的拼音就是聚族索引。也就是说聚族索引的组织顺序和数据本身的组织顺序是一致的 ,这也解释了数据库中只能定义一个聚族索引的原因,因为数据本身只能按一种方式进行排序。
          那聚族索引有什么特别的好处呢,这个好处就是在数据库中执行查找一批数据的语句会比较快,因为数据已经按照聚族索引排好序了,很少的io操作就可以将数据从库中取出。好比你在字典中查找发音从从a到c的汉字,只需要查到a的开始页和c的结束页,中间的所有页都符合查询要求,不用再一页一页地查找。

     

     

    • 非聚族索引

          非聚族索引就好比字典里的偏旁、笔画索引, 索引组织顺序和数据组织顺序不一致 ,因此非聚族索引可以创建多个。当查找一条数据时,非聚族索引和聚族索引的效率相差不大,但查找一批数据(n)时,非聚族索引需要的io可能是聚族索引的n倍,因为非聚族索引需要一条一条地进行查找。

     

    转载于:https://my.oschina.net/sniperLi/blog/356359

    展开全文
  • oracle索引原理

    千次阅读 2017-07-11 09:47:16
    一、索引原理   Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁,并导致数据库性能急剧下降或进程终止。而如果做出正确的选择,则...

    一、索引原理

        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索引时的技巧

    索引对数据库访问的性能的作用十分巨大,设计合理的索引对于系统性能调整至关重要。而使用索引又是数据库开发过程   中最困难的一点。在设计索引的时候要注意以下几个方面:

    [1]在OLTP系统中,尽量避免全表扫描,尽量使绝大多数操作都通过索引访问

    [2]数据量很大并且经常变动的表上的索引不易过多,过量的索引会导致插入、更新和删除操作变慢,产生大量的IO,如果一张表上的索引超过8个,就需要检查是否这些索引都是必要的。(但是要值得注意的是这条原则很可能被过度的夸大。因为绝大多数OLTP系统,写操作不足10%,绝大多数的操作是读。因此如果是设计的合理,并不能说一张表上的索引超过多少就是不合理的)

    [3]如果索引数量过多,建议删除部分所有的列都建有独立索引的复合索引,复合查询操作可以通过使用两个独立列的索引结果集合并来获取,也可以保障查询效率

    [4]小表不要建立索引,可能通过索引访问速度更慢,把小表放入KEEP 池效率更高

    [5]索引的PCTFREE、INITTRANS、MAXTRANS参数设置十分重要,特别是对于变化十分大的索引

    [6]对于比较大的索引,使用索引分区会改善效率

    [7]位图索引对于列表类值的效果较好(索引大小也比较小),但是位图索引不适合变化十分频繁的表

    [8]使用函数索引可以避免大量不必要的全表扫描

    [9]如果索引包含了查询需要的所有信息,查询就不需要访问表的数据,可以大大提高访问效率,因此要注意建立合理的复合索引,并注意SQL语句的合理性

    [10]用好索引组织表

    [11]可以使用反转索键值索引来消除索引相关的热块

    [12]随着数据的变化,索引的效率会下降,因此定期重建索引对于性能提升有很大帮助

    [13]使用CBO优化器的情况下,保证表和索引的数据得到了良好的分析是保证优化器选择最佳执行计划的关键

    [14]删除所有不必要的索引。

     

    三、具体例子

    当WHERE条件中的字段是类似column1 is null 或者column1 is not null是,即便column1上面本来有索引也不会用到

    比如where column1 =‘aaa’ 是可以用到索引的

    下面的情况都用不到索引
    column1||column2=‘aaabbb’
    Substr(column1,1)=‘aaa’
    Column1||’b’ =‘aaab’
    Column3 + 1 > :a

    Like 的字符串中第一个字符如果是‘%’则用不到索引
    Column1 like ‘aaa%’ 是可以的
    Column1 like ‘%aaa%’用不到

    <>也用不到索引
    有时可以采取适当措施改写后可以用到索引
    例:column1 有3个值(‘A’,’B’,’C’),三个值的分布为‘A’ 10%, ‘B’ 80%,’C’ 10%,则column1 <> ‘B’ 可以改写为column1 in(‘A’,’C’)

    尽管In写法要比exists简单一些,exists一般来说性能要比In要高的多
    用In还是用Exists的时机
    当in的集合比较小的时候,或者用Exists无法用到选择性高的索引的时候,用In要好,否则就要用Exists
    例:select count(*) from person_info where xb in (select xb_id from dic_sex);
    Select count(*) from n_acntbasic a where shbxdjm =:a and exists(select 1 from person_info where pid=a.pid and …);

    Select * from person_info where zjhm=3101….;将会对person_info全表扫描
    Select * from person_info where zjhm =‘3101…’才能用到索引

    假定TEST表的dt字段是date类型的并且对dt建了索引。
    如果要查‘20041010’一天的数据.下面的方法用不到索引
    Select * from test where to_char(dt,’yyyymmdd’) =‘20041010’;
    而select * from test where dt >=to_date(‘20041010’,’yyyymmdd’) and dt < to_date(‘20041010’,’yyyymmdd’) + 1 将会用到索引。

    如果能不用到排序,则尽量避免排序。
    用到排序的情况有
    集合操作。Union ,minus ,intersect等,注:union all 是不排序的。
    Order by
    Group by
    Distinct
    In 有时候也会用到排序
    确实要排序的时候也尽量要排序小数据量
    ,尽量让排序在内存中执行,有文章说,内存排序的速度是硬盘排序的1万倍。

    展开全文
  • ORACLE索引原理

    2017-08-29 19:36:38
    B-TREE索引(二叉树索引,默认情况下,我们建的索引都是此种类型)  一个B树索引只有一个根节点,它实际就是位于树的最顶端的分支节点。可以用下图一来描述B树索引的结构。其中,B表示分支节点,而L表示叶子节点...
  • oracle 索引原理

    2015-08-01 20:30:14
    B-TREE索引 一个B树索引只有一个根节点,它实际就是位于树的最顶端的分支节点。 可以用下图一来描述B树索引的结构。其中,B表示分支节点,而L表示叶子节点。  对于分支节点块(包括根节点块)来说,其所...
  • Oracle 索引原理和种类

    2018-03-19 18:01:33
    Oracle索引原理

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 838
精华内容 335
关键字:

oracle索引原理