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

    千次阅读 2014-12-17 17:38:55
    Oracle索引原理 Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁,并导致数据库性能急剧下降或进程终止。而如果做出正确的选择,则可以...
    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索引 
         索引编排表 
         反转键索引 
         基于函数的索引 
         分区索引 
         本地和全局索引
         
    逻辑上:
    Single column 单行索引
    Concatenated 多行索引
    Unique 唯一索引
    NonUnique 非唯一索引
    Function-based函数索引
    Domain 域索引


    物理上:
    Partitioned 分区索引(全局索引和本地索引)




    NonPartitioned 非分区索引
    B-tree:
    Normal 正常型B树
    Rever Key 反转型B树 
    Bitmap 位图索引


    索引结构:
    B-tree:
    适合与大量的增、删、改(OLTP);
    不能用包含OR操作符的查询;
    适合高基数的列(唯一值多)
    典型的树状结构;
    每个结点都是数据块;
    大多都是物理上一层、两层或三层不定,逻辑上三层;
    叶子块数据是排序的,从左向右递增;
    在分支块和根块中放的是索引的范围;
    Bitmap:
    适合与决策支持系统;
    做UPDATE代价非常高;
    非常适合OR操作符的查询; 
    基数比较少的时候才能建位图索引;
    树型结构:
    索引头 
    开始ROWID,结束ROWID(先列出索引的最大范围)
    BITMAP
    每一个BIT对应着一个ROWID,它的值是1还是0,如果是1,表示着BIT对应的ROWID有值;


    B*tree索引的话通常在访问小数据量的情况下比较适用,比如你访问不超过表中数据的5%,当然这只是个相对的比率,适用于一般的情况。bitmap的话在数据仓库中使用较多,用于低基数列,比如性别之类重复值很多的字段,基数越小越好。


    关于分区表和分区索引(About Partitioned Tables and Indexes)对于10gR2而言,基本上可以分成几类: 


       Range(范围)分区 
       Hash(哈希)分区 
       List(列表)分区 
       以及组合分区:Range-Hash,Range-List。


      对于表而言(常规意义上的堆组织表),上述分区形式都可以应用(甚至可以对某个分区指定compress属性),只不过分区依赖列不能是lob,long之类数据类型,每个表的分区或子分区数的总数不能超过1023个。


      对于索引组织表,只能够支持普通分区方式,不支持组合分区,常规表的限制对于索引组织表同样有效,除此之外呢,还有一些其实的限制,比如要求索引组织表的分区依赖列必须是主键才可以等。


      注:本篇所有示例仅针对常规表,即堆组织表!


      对于索引,需要区分创建的是全局索引,或本地索引:


      l 全局索引(global index):即可以分区,也可以不分区。即可以建range分区,也可以建hash分区,即可建于分区表,又可创建于非分区表上,就是说,全局索引是完全独立的,因此它也需要我们更多的维护操作。


      l 本地索引(local index):其分区形式与表的分区完全相同,依赖列相同,存储属性也相同。对于本地索引,其索引分区的维护自动进行,就是说你add/drop/split/truncate表的分区时,本地索引会自动维护其索引分区。


      Oracle建议如果单个表超过2G就最好对其进行分区,对于大表创建分区的好处是显而易见的,这里不多论述why,而将重点放在when以及how。


      WHEN


      一、When使用Range分区


      Range分区呢是应用范围比较广的表分区方式,它是以列的值的范围来做为分区的划分条件,
    将记录存放到列值所在的range分区中,比如按照时间划分,2008年1季度的数据放到a分区,08年2季度的数据放到b分区,因此在创建的时候呢,需要你指定基于的列,以及分区的范围值,如果某些记录暂无法预测范围,可以创建maxvalue分区,所有不在指定范围内的记录都会被存储到maxvalue所在分区中,并且支持指定多列做为依赖列,后面在讲how的时候会详细谈到。


      二、When使用Hash分区


      通常呢,对于那些无法有效划分范围的表,可以使用hash分区,这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中,列所在分区是依据分区列的hash值自动分配,因此你并不能控制也不知道哪条记录会被放到哪个分区中,hash分区也可以支持多个依赖列。


      三、When使用List分区


      List分区与range分区和hash分区都有类似之处,该分区与range分区类似的是也需要你指定列的值,但这又不同与range分区的范围式列值---其分区值必须明确指定,也不同与hash分区---通过明确指定分区值,你能控制记录存储在哪个分区。它的分区列只能有一个,而不能像range或者hash分区那样同时指定多个列做为分区依赖列,不过呢,它的单个分区对应值可以是多个。


      你在分区时必须确定分区列可能存在的值,一旦插入的列值不在分区范围内,则插入/更新就会失败,因此通常建议使用list分区时,要创建一个default分区存储那些不在指定范围内的记录,类似range分区中的maxvalue分区。


      四、When使用组合分区


      如果某表按照某列分区之后,仍然较大,或者是一些其它的需求,还可以通过分区内再建子分区的方式将分区再分区,即组合分区的方式。


      组合分区呢在10g中有两种:range-hash,range-list。注意顺序哟,根分区只能是range分区,子分区可以是hash分区或list分区。


      提示:11g在组合分区功能这块有所增强,又推出了range-range,list-range,list-list,list-hash,这就相当于除hash外三种分区方式的笛卡尔形式都有了。为什么会没有hash做为根分区的组合分区形式呢,再仔细回味一下第二点,你一定能够想明白~~。


    Oracle数据库中,有两种类型的分区索引,全局索引和本地索引,其中本地索引又可以分为本地前缀索引和本地非前缀索引。下面就分别看看每种类型的索引各自的特点。


    全局索引以整个表的数据为对象建立索引,索引分区中的索引条目既可能是基于相同的键值但是来自不同的分区,也可能是多个不同键值的组合。


    全局索引既允许索引分区的键值和表分区键值相同,也可以不相同。全局索引和表之间没有直接的联系,这一点和本地索引不同。


    SQL> create table orders (
         order_no      number,
         part_no       varchar2(40),
         ord_date      date
         )
         partition by range (ord_date)
          (partition Q1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY')),
           partition Q2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
           partition Q3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
           partition Q4 values less than (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
          )
         ;


    Table created.


    SQL> create index orders_global_1_idx
         on orders(ord_date)
          global partition by range (ord_date)
           (partition GLOBAL1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY')),
            partition GLOBAL2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
            partition GLOBAL3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
            partition GLOBAL4 values less than (MAXVALUE)
           )
         ;


    Index created.


    SQL> create index orders_global_2_idx
         on orders(part_no)
          global partition by range (part_no)
           (partition IND1 values less than (555555),
            partition IND2 values less than (MAXVALUE)
           )
         ;


    Index created.


    从上面的语句可以看出,全局索引和表没有直接的关联,必须显式的指定maxvalue值。假如表中新加了分区,不会在全局索引中自动增加新的分区,必须手工添加相应的分区。


    SQL> alter table orders add partition Q5 values less than (TO_DATE('01-APR-2000','DD-MON-YYYY'));


    Table altered.


    SQL> select TABLE_NAME, PARTITION_NAME from dba_tab_partitions where table_name='ORDERS';


    TABLE_NAME                     PARTITION_NAME
    ------------------------------ ------------------------------
    ORDERS                         Q1
    ORDERS                         Q2
    ORDERS                         Q3
    ORDERS                         Q4
    ORDERS                         Q5


    SQL> select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_global_1_idx');


    INDEX_NAME                     PARTITION_NAME
    ------------------------------ ------------------------------
    ORDERS_GLOBAL_1_IDX            GLOBAL1
    ORDERS_GLOBAL_1_IDX            GLOBAL2
    ORDERS_GLOBAL_1_IDX            GLOBAL3
    ORDERS_GLOBAL_1_IDX            GLOBAL4


    使用全局索引,索引键值必须和分区键值相同,这就是所谓的前缀索引。Oracle不支持非前缀的全局分区索引,如果需要建立非前缀分区索引,索引必须建成本地索引。


    SQL> create index orders_global_2_idx
      2  on orders(part_no)
      3   global partition by range (order_no)
      4    (partition IND1 values less than (555555),
      5     partition IND2 values less than (MAXVALUE)
      6    )
      7  ;
     global partition by range (order_no)
                                        *
    ERROR at line 3:
    ORA-14038: GLOBAL partitioned index must be prefixed


    接下来再来看看本地分区。


    本地索引的分区和其对应的表分区数量相等,因此每个表分区都对应着相应的索引分区。使用本地索引,不需要指定分区范围因为索引对于表而言是本地的,当本地索引创建时,Oracle会自动为表中的每个分区创建独立的索引分区。


    创建本地索引不必显式的指定maxvalue值,因为为表新添加表分区时,会自动添加相应的索引分区。


    create index orders_local_1_idx
    on orders(ord_date)
     local
      (partition LOCAL1,
       partition LOCAL2,
       partition LOCAL3,
       partition LOCAL4
      )
    ;


    Index created.


    SQL> select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_local_1_idx');


    INDEX_NAME                     PARTITION_NAME
    ------------------------------ ------------------------------
    ORDERS_LOCAL_1_IDX             LOCAL1
    ORDERS_LOCAL_1_IDX             LOCAL2
    ORDERS_LOCAL_1_IDX             LOCAL3
    ORDERS_LOCAL_1_IDX             LOCAL4


    SQL> alter table orders add partition Q5 values less than (TO_DATE('01-APR-2000','DD-MON-YYYY'));


    Table altered.


    SQL> select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_local_1_idx');


    INDEX_NAME                     PARTITION_NAME
    ------------------------------ ------------------------------
    ORDERS_LOCAL_1_IDX             LOCAL1
    ORDERS_LOCAL_1_IDX             LOCAL2
    ORDERS_LOCAL_1_IDX             LOCAL3
    ORDERS_LOCAL_1_IDX             LOCAL4
    ORDERS_LOCAL_1_IDX             Q5


    这里系统已经自动以和表分区相同的名字自动创建了一个索引分区。同理,删除表分区时相对应的索引分区也自动被删除。


    本地索引和全局索引还有一个显著的差别,就是上面提到的,本地索引可以创建成本地非前缀型,而全局索引只能是前缀型。


    SQL> create index orders_local_2_idx
         on orders(part_no)
          local
           (partition LOCAL1,
            partition LOCAL2,
            partition LOCAL3,
            partition LOCAL4)
         ;


    Index created.


    SQL> select INDEX_NAME, PARTITION_NAME, HIGH_VALUE from dba_ind_partitions
         where index_name=upper('orders_local_2_idx');


    INDEX_NAME                     PARTITION_NAME                 HIGH_VALUE
    ------------------------------ ------------------------------ ---------------------------------------------------------
    ORDERS_LOCAL_2_IDX             LOCAL1                         TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
                                                                  'NLS_CALENDAR=GREGORIA'
    ORDERS_LOCAL_2_IDX             LOCAL2                         TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
                                                                  'NLS_CALENDAR=GREGORIA'
    ORDERS_LOCAL_2_IDX             LOCAL3                         TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
                                                                  'NLS_CALENDAR=GREGORIA'
    ORDERS_LOCAL_2_IDX             LOCAL4                         TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
                                                                  'NLS_CALENDAR=GREGORIA'


    从上面的输出可以看出,虽然索引的键值是part_no,但索引分区的键值仍然和表的分区键值相同,即ord_date,也即是所谓的非前缀型索引。


    最后,再引用一个例子说明前缀索引和非前缀索引的应用。


    假设有一个使用DATE列分区的大表。我们经常使用一个VARCHAR2列(VCOL)进行查询,但这个列并不是表的分区键值。


    有两种可能的方法来访问VCOL列的数据,一是建立基于VCOL列的本地非前缀索引,


                   |                                         |
                 -------                                   -------
                |       |         (10 more                |        |
    Values:     A..     Z..   partitions here)            A..      Z..


    另一种是建立基于VCOL列的全局索引,


                    |                                         |
                  -------                                   -------
                 |       |         (10 more                |        |
    Values:      A..     D..   partitions here)            T..      Z..


    可以看出,如果能够保证VCOL列值的唯一性,全局索引将会是最好的选择。如果VCOL列值不唯一,就需要在本地非前缀索引的并行查询和全局索引顺序查询以及高昂的维护代价之间做出选择。








    Oracle B-tree索引的浅析
    如果聚簇因子过大,那么重建索引可能会有好处,聚簇因子应该接近块的数量,而非行的数量。
    Oracle索引经典的神话
    ·索引会随着时间的增加而变的不平衡;
    ·删除的索引空间不会被重用;
    ·随着索引层数的增加,索引将会变得无效并需要重建;
    ·聚簇因子差,索引需要重建;
    ·为了提高性能,索引需要经常重建;
    索引基础
           ·一个更新由一个删除和一个插入组成;
           ·页块由索引条目(row header(2/3B)|length(1B)|indexed data value(nB)|length(1B)|RowID(6B)
    )和相应的rowid组成;
           ·每个页块包含两个指针分别前面的页块和后面页块;
    Treedump
    alter session set events ‘immediate trace name treedump level index_object_id’;
           ----- begin tree dump
    branch: 0x424362 4342626 (0: nrow: 2, level: 1)
       leaf: 0x424363 4342627 (-1: nrow: 540 rrow: 540)
       leaf: 0x424364 4342628 (0: nrow: 461 rrow: 461)
    ----- end tree dump
           以上dump包含的信息如下:
    块类型:
    branch(分支块);leaf(页块);
        块地址:0x424362 4342626;
        nrow:索引条目的数量;
        rrow:当前块中的索引条目数量;
        level:分支块等级(页块隐示为0);
    Block Dump
    alter system dump datafile X block X;
    alter system dump datafile X block min X1 block max X2
             Start dump data blocks tsn: 0 file#: 1 minblk 148538 maxblk 148538
    buffer tsn: 0 rdba: 0x0042443a (1/148538)
    scn: 0x0000.00162a95 seq: 0x01 flg: 0x04 tail: 0x2a950601
    frmt: 0x02 chkval: 0x8b5c type: 0x06=trans data
    Block header dump:  0x0042443a
     Object id on Block? Y
     seg/obj: 0xd1fe  csc: 0x00.162a95  itc: 2  flg: O  typ: 2 - INDEX
             fsl: 0  fnx: 0x42443b ver: 0x01
         
    Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
    0x01   0x0005.02a.00000332  0x008005cb.020e.01  CB--    0  scn 0x0000.00162a92
    0x02   0x0008.011.00000346  0x008002e6.0163.03  C---    0  scn 0x0000.00162a93
    该dump包含的信息如下:
    rdba:分支块的相对数据库块地址(文件号/块号);
    scn:块最后改变的SCN号;
    type:块类型;
    seq:块改变的数量;
    seg/obj: 16进制对象ID;
    typ:段类型;
    Itl:相关的事务槽(页块默认为2),包括槽ID,事务ID,撤销块地址,标记,锁信息,和事务SCN;
    通过rba确定数据文件号和块号:
    select    DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(rba),
    DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(rba)
    from dual;
    通用的索引块头
           
    header address 153168988=0x9212c5c
    kdxcolev 0
    KDXCOLEV Flags = - - -
    kdxcolok 1
    kdxcoopc 0x89: opcode=9: iot flags=--- is converted=Y
    kdxconco 2
    kdxcosdc 2
    kdxconro 254
    kdxcofbo 544=0x220
    kdxcofeo 4482=0x1182
    kdxcoavs 3938
           
    kdxcolev:索引级别(0代表页块);
        kdxcolok:标示结构块事块是否发生;
        kdxcoopc:内部操作码;
        kdxconco:索引列数量,包括ROWID;
        kdxcosdc:块中索引结构改变的数量;
        kdxconro:索引条目的数量,不包括kdxbrlmc指针;
        kdxcofbo:块中空闲空间的开始位置;
        kdxcofeo:块中空闲空间的结束位置;
        kdxcoavs:块中的可用空间数量(kdxcofbo-kdxcofeo);
    分支头区域
           kdxbrlmc 8388627=0x800013
    kdxbrsno 92
    kdxbrbksz 8060
           kdxbrlmc:如果索引值小于第一个值(row#0),则为该索引值所在的块地址;
           kdxbrsno:最后更改的索引条目;
           kdxbrbksz:可使用的块空间;
    叶块头区域
           
    kdxlespl 0
    kdxlende 127
    kdxlenxt 4342843=0x42443b
    kdxleprv 4342845=0x42443d
    kdxledsz 0
    kdxlebksz 8036
           
    kdxlespl:块拆分时被清除的未提交数据的字节数;
        kdxlende:被删除的条目数;
        kdxlenxt:下一个页块的RBA;
        kdxleprv:上一个页块的RBA;
        kdxlebksz:可使用的块空间(默认小于分支的可用空间);
         分支条目
           
    row#0[7898] dba: 4342821=0x424425
    col 0; len 3; (3):  c2 61 03
    col 1; TERM
    row#1[7214] dba: 4342873=0x424459
    col 0; len 4; (4):  c3 04 02 17
    col 1; TERM
           行号,[块中的起始位置] dba;
           列号,列长度,列值;
           brach中的每个entry有2个columns:
    一个是child blocks中的最大值,另一个是指向的下一层block的address'
        但是某些时候可能会有一些比较奇怪的结果:
           
    row#0[7025] dba: 4342908=0x42447c
    col 0; len 1024; (1024): 
     41 20 20 20 …20
    col 1; len 4; (4):  00 42 44 73
    ----- end of branch block dump -----
    具体待补充。。。
    叶条目
    row#38[5014] flag: ----S-, lock: 2, len=14
    col 0; len 4; (4):  c3 04 61 55
    col 1; len 6; (6):  00 42 43 db 00 a1
    row#39[5028] flag: ---DS-, lock: 2, len=14
           行号[在块中的开始位置] 各种标记(锁信息,删除信息);
           索引列号,长度,值。其中6个字节的为ROWID号,将其转换为二进制,算法结果为:
    前10 bit代表了file_id
    中22 bit代表了block_id
    后16 bit代表了row_id;
    通过文件号和块号算出的结果为创建该索引的表的块。
    奇怪的是,为什么索引中的rowid不能直接找到obj_id?
    因为索引段对应的数据段在 一开始就知道,因为是先知道数据段才找到索引段,然后
    根据索引段内容去搜索数据段内容,所以索引段中 rowid 不必包含 data_object_id 信息。
           如果索引是建立在非分区表上,或者是分区表上的 LOCAL 索引,使用的是6 bytes的 Restricted ROWID
    。如果索引是建立在分区表上的 GLOBAL index,则使用 10bytes 的 Extended ROWID,这样可以区分索引指向哪个分区表。
           
     
    更新/重用索引条目
           当更新了索引条目后,DUMP如下:
           kdxconco 2
    kdxcosdc 0
    kdxconro 2
    kdxcofbo 40=0x28
    kdxcofeo 8006=0x1f46
    kdxcoavs 7966
    kdxlespl 0
    kdxlende 1
    kdxlenxt 0=0x0
    kdxleprv 0=0x0
    kdxledsz 0
    kdxlebksz 8036
    row#0[8021] flag: ---D-, lock: 2 => deleted index entry
    col 0; len 5; (5): 42 4f 57 49 45
    col 1; len 6; (6): 00 80 05 0a 00 00
    row#1[8006] flag: -----, lock: 2
    col 0; len 5; (5): 5a 49 47 47 59 => new index entry
    col 1; len 6; (6): 00 80 05 0a 00 00
           更新后,将包含一个删除的条目,一个新的条目。在随后的插入中,如果新插入的索引条目能够放到被删除的索引条目的位置上,就会直接重用这个条目。根据索引值来决定。
           所谓重用,是对row 的重用,而不是对row所在物理存储(或说物理位置)的重用。索引是按照indexed value对row进行排序的。有新的row被插入,首先按照value排序,将他放在合适的row list中,如果他的位置正好原来有个row被删掉了,则重用这个row在row list中的位置。至于物理存储上,则可能根据版本不同会有不同。在10.2中,我做的测试并没有向下开辟空间。
           结论
    ·到叶块中的任何插入都将移除所有被删除的条目;
           ·删除的空间在随后的写中被清除;
           ·删除的空间在延迟块清除中被清除;
           ·全空块被放在空闲列表,可以重用;
           
    索引统计
    ·dba_indexes
    ·dbms_stats
    ·index_stats
             -- analyze index index_name validate structure;
             --分析资源,锁;
    ·v$segment_statistics
      statistics_level = typical (or all)
           注意点:
             blevel (dba_indexes) vs. height (index_stats)
             blocks allocated,但未必使用;
             lf_rows_len包含行负载(单列索引12个字节)
             pct_used索引结构中当前使用的空间:(used_space/btree_space)*100
             大多数索引统计包含删除的条目:
                  non-deleted rows = lf_rows – del_lf_rows
                  pct_used by non-deleted rows = ((used_space – del_lf_rows_len) / btree_space) * 100
    展开全文
  • 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 索引原理和种类

    2018-03-19 18:01:33
    Oracle索引原理
    • 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倍,因为非聚族索引需要一条一条地进行查找。

     

    展开全文
  • oracle索引原理

    千次阅读 2006-06-02 11:46:00
    [1] 基本的索引概念查询DBA_INDEXES视图可得到表中所有...[2] 组合索引当某个索引包含有多个已索引的列时,称这个索引为组合(concatented)索引。在 Oracle9i引入跳跃式扫描的索引访问方法之前,查询只能在有限条件下

    [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索引原理

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

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

    2013-08-18 10:48:01
    (本文内容均整理自万能的INTERNET)... 对于分支节点块(包括根节点块)来说,其所包含的索引条目都是按照顺序排列的(缺省是升序排列,也可以在创建索引时指定为降序排列)。每个索引条目(也可以叫做每条记录)都具
  • oracle 索引原理

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

    2017-09-27 15:05:58
    索引是一种允许直接访问数据表中某一数据行的树型结构...索引一旦被建立就将被Oracle系统自动维护,查询语句中不用指定使用哪个索引。  分类可以按逻辑设计和物理实现来分类。 索引逻辑分类 单列索引:基于一

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 16,873
精华内容 6,749
关键字:

oracle组合索引原理