精华内容
下载资源
问答
  • ORACLE位图索引是最适用于数据仓库或决策支持系统(DSS)的索引模型。数据仓库中常见的一种模式是星型模式。其中包括中央事实表和数个相关的维度表。前者包含特定主题的全部信息。如客户的收支,后者包含特定维度...

    ORACLE的位图索引是最适用于数据仓库或决策支持系统(DSS)的索引模型。数据仓库中常见的一种模式是星型模式。其中包括中央事实表和数个相关的维度表。前者包含特定主题的全部信息。如客户的收支,后者包含特定维度比如说时间维度或者地理维度的相关引用类型数据。在星型模式中,维度表是父表,而中央事实表是子表。以下这就是个简单的星型模式示例:

    在ORACLE数据中,专门针对星型模式进行了配置设计我们称之为星型转换。他是专门用来提高星型模式的查询性能。若是要使用星型转换,需要配置一些ORACLE的初始化参数。此外,为了使星型转换能够发生,最低限度使必须在星型模式的事实表的外键上设置位图索引。虽然,现在很多文章或者教程中已经特殊说明了不建议使用外键,但是对于一些强关系模型来说,设置外键还是事倍功半的。

    那么哪些情况适用于位图索引呢?他的优缺点又是什么呢?我们看看下面的条目就一目了然了:

    1 位图索引一般应用于低基数列(关于索引基数我们会在后面的文章中涉及)

    2 位图索引最好用于数据仓库和DSS系统

    3 位图索引最好用于星型模式 当然了雪花模型也是可以使用的 但是要考虑到雪花构型的花瓣复杂度

    4 对于在索引列上有许多链接查询或者过滤查询 位图索引是十分高效的

    5 对于启用了位图索引的表 一定一定要尽量减少或者避免DML操作尤其是UPDATE

    6 如果你非要对表执行DML操作 ,应在对表进行更新之前删除位图索引,等DML操作完成之后再重新建立

    7 如果要在分区表上执行DML操作,应该吧发生DML的分区的索引设置为不可用,并在DML活动完成之后重新创建分区索引。

    总之就是折腾对不对?!对于OLTP(联机事务处理系统),使用位图索引是非常不合适的。因为在这里操作中,会有大量的锁产生。而使位图索引出现行锁定的问题。会极大的影响性能。

     

    那么位图索引和我们上一篇文章说的B*传统索引究竟有多大的不同呢?请看图:

    大家看到了吧!发没发现点小问题。

     

    位图索引,顾名思义,肯定与“位”有关。在这里相比知道,计算机中的所有信息最终都是通过“位/bit”来运算, 而二进制位运算在计算机中是非常高效的。(你别告诉我你不知道原因?计算机就是一个典型的二进制模型)每一个二进制位都可以取值0或者1,而取值的确切含义是由具体的上下文环境决定的(参考计算机《计算机原理》一书)。在ORACLE位图索引中,每一个二进制位代表了某一行中索引列的取值情况。例如,上面标识颜色列的位图索引结构如下:

    blue:1000100100010010100

    green :0001010000100100000

    red :0100000011000001001

    yellow :0010001000001000010

    在上面的位图结构中,存储了19条记录的分布情况,以blue为例,从左到右的第n个二进制位代表了第n条记录是否为blue,如果二进制位为1,代表true即是,0代表false即性别不是。以此类推。

    大家都知道,在oracle中是根据rowid来定位记录的,因此,我们需要引入start rowid和end rowid,通过start rowid ,end rowid 和二进制位的偏移,我们就可以非常快速的计算出二进制位所代表的表记录rowid。位图索引的最终逻辑结构如下图:

     

    位图索引的查询性能究竟怎么样?!

     

    在很多资料中,都可以看到这样的论述:位图索引适合于 low distict cardinality(低基数)的列。实际上,对于high distinct cardinality (高基数)的列,位图索引的查询性能也是非常不错的。下面我们来验证这个结论。

    以下实验环境使用的事ORCLE 11G R2 11.2.0.4版本:

    首先我们创建两张表: emp_l和emp_h.
    SQL> create table emp_l(empno number(10), ename varchar2(30), sal number(10));

    表已创建。

    Begin
     For i in 1..1000000
      Loop
       Insert into emp_l
        values(i, dbms_random.string('U',30), dbms_random.value(1000,7000));
         If mod(i, 10000) = 0 then
           Commit;
         End if;
     End loop;
    End;
     /

    PL/SQL 过程已成功完成。

    SQL> create table emp_h as select /* +append */ * from emp_l order by dbms_random.random;
    emp_h由于其记录是随机分布的,因此该表上索引的CLUSTERING_FACTOR要高一些。
    我们首先看一下emp_l表等值查询情况下,索引的效率如何:
    SQL> create bitmap index bm_normal on emp_l(empno);

    索引已创建。


    SQL> analyze table emp_l compute statistics for table for all indexes for all indexed columns;

    表已分析。

    SQL> select index_name,clustering_factor from user_indexes;

    INDEX_NAME      CLUSTERING_FACTOR
    ------------------------------ -----------------
    BM_NORMAL     1000000

    SQL> set autot traceonly
    SQL> select * from emp_l where empno=&empno;
    输入 empno 的值: 1000
    原值  1: select * from emp_l where empno=&empno
    新值  1: select * from emp_l where empno=1000


    执行计划
    ----------------------------------------------------------
    Plan hash value: 1526426521

    -------------------------------------------------------------------------------------------
    | Id | Operation     | Name  | Rows | Bytes | Cost (%CPU)| Time  |
    -------------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT    |   | 1 |  34 | 3  (0)| 00:00:01 |
    |  1 | TABLE ACCESS BY INDEX ROWID | emp_l | 1 |  34 | 3  (0)| 00:00:01 |
    |  2 |  BITMAP CONVERSION TO ROWIDS|   |  |  |     |  |
    |* 3 |  BITMAP INDEX SINGLE VALUE | BM_NORMAL |  |  |     |  |
    -------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

     3 - access("EMPNO"=1000)


    统计信息
    ----------------------------------------------------------
      1 recursive calls
      0 db block gets
      5 consistent gets
      0 physical reads
      0 redo size
     702 bytes sent via SQL*Net to client
     520 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      1 rows processed

    SQL> select * from emp_l where empno=&empno;
    输入 empno 的值: 2398
    原值  1: select * from emp_l where empno=&empno
    新值  1: select * from emp_l where empno=2398


    执行计划
    ----------------------------------------------------------
    Plan hash value: 1526426521

    -------------------------------------------------------------------------------------------
    | Id | Operation     | Name  | Rows | Bytes | Cost (%CPU)| Time  |
    -------------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT    |   | 1 |  34 | 3  (0)| 00:00:01 |
    |  1 | TABLE ACCESS BY INDEX ROWID | emp_l | 1 |  34 | 3  (0)| 00:00:01 |
    |  2 |  BITMAP CONVERSION TO ROWIDS|   |  |  |     |  |
    |* 3 |  BITMAP INDEX SINGLE VALUE | BM_NORMAL |  |  |     |  |
    -------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

     3 - access("EMPNO"=2398)


    统计信息
    ----------------------------------------------------------
      1 recursive calls
      0 db block gets
      5 consistent gets
      0 physical reads
      0 redo size
     703 bytes sent via SQL*Net to client
     520 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      1 rows processed

    SQL> select * from emp_l where empno=&empno;
    输入 empno 的值: 8545
    原值  1: select * from emp_l where empno=&empno
    新值  1: select * from emp_l where empno=8545


    执行计划
    ----------------------------------------------------------
    Plan hash value: 1526426521

    -------------------------------------------------------------------------------------------
    | Id | Operation     | Name  | Rows | Bytes | Cost (%CPU)| Time  |
    -------------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT    |   | 1 |  34 | 3  (0)| 00:00:01 |
    |  1 | TABLE ACCESS BY INDEX ROWID | emp_l | 1 |  34 | 3  (0)| 00:00:01 |
    |  2 |  BITMAP CONVERSION TO ROWIDS|   |  |  |     |  |
    |* 3 |  BITMAP INDEX SINGLE VALUE | BM_NORMAL |  |  |     |  |
    -------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

     3 - access("EMPNO"=8545)


    统计信息
    ----------------------------------------------------------
      1 recursive calls
      0 db block gets
      5 consistent gets
      0 physical reads
      0 redo size
     703 bytes sent via SQL*Net to client
     520 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      1 rows processed

    SQL> select * from emp_l where empno=&empno;
    输入 empno 的值: 128444
    原值  1: select * from emp_l where empno=&empno
    新值  1: select * from emp_l where empno=128444


    执行计划
    ----------------------------------------------------------
    Plan hash value: 1526426521

    -------------------------------------------------------------------------------------------
    | Id | Operation     | Name  | Rows | Bytes | Cost (%CPU)| Time  |
    -------------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT    |   | 1 |  34 | 3  (0)| 00:00:01 |
    |  1 | TABLE ACCESS BY INDEX ROWID | emp_l | 1 |  34 | 3  (0)| 00:00:01 |
    |  2 |  BITMAP CONVERSION TO ROWIDS|   |  |  |     |  |
    |* 3 |  BITMAP INDEX SINGLE VALUE | BM_NORMAL |  |  |     |  |
    -------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

     3 - access("EMPNO"=128444)


    统计信息
    ----------------------------------------------------------
      1 recursive calls
      0 db block gets
      5 consistent gets
      0 physical reads
      0 redo size
     704 bytes sent via SQL*Net to client
     520 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      1 rows processed

    SQL> drop index bm_normal;

    索引已删除。

    SQL> create index bt_normal on emp_l(empno);

    索引已创建。

    SQL> analyze table emp_l compute statistics for table for all indexes for all indexed columns;

    表已分析。

    SQL> select index_name,clustering_factor from user_indexes;

    INDEX_NAME      CLUSTERING_FACTOR
    ------------------------------ -----------------
    BT_NORMAL      6210
    SYS_IL0000076897C00002$$
    PK_EMP         1
    PK_DEPT         1

    SQL> set autot traceonly
    SQL> select * from emp_l where empno=&empno;
    输入 empno 的值: 1000
    原值  1: select * from emp_l where empno=&empno
    新值  1: select * from emp_l where empno=1000


    执行计划
    ----------------------------------------------------------
    Plan hash value: 733975378

    ------------------------------------------------------------------------------------------
    | Id | Operation    | Name  | Rows | Bytes | Cost (%CPU)| Time  |
    ------------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT   |   |   1 |  34 |   4  (0)| 00:00:01 |
    |  1 | TABLE ACCESS BY INDEX ROWID| emp_l |   1 |  34 |   4  (0)| 00:00:01 |
    |* 2 |  INDEX RANGE SCAN   | BT_NORMAL |   1 |  |   3  (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

     2 - access("EMPNO"=1000)


    统计信息
    ----------------------------------------------------------
      1 recursive calls
      0 db block gets
      5 consistent gets
      0 physical reads
      0 redo size
     702 bytes sent via SQL*Net to client
     520 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      1 rows processed

    SQL> select * from emp_l where empno=&empno;
    输入 empno 的值: 128444
    原值  1: select * from emp_l where empno=&empno
    新值  1: select * from emp_l where empno=128444


    执行计划
    ----------------------------------------------------------
    Plan hash value: 733975378

    ------------------------------------------------------------------------------------------
    | Id | Operation    | Name  | Rows | Bytes | Cost (%CPU)| Time  |
    ------------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT   |   |   1 |  34 |   4  (0)| 00:00:01 |
    |  1 | TABLE ACCESS BY INDEX ROWID| emp_l |   1 |  34 |   4  (0)| 00:00:01 |
    |* 2 |  INDEX RANGE SCAN   | BT_NORMAL |   1 |  |   3  (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

     2 - access("EMPNO"=128444)


    统计信息
    ----------------------------------------------------------
      1 recursive calls
      0 db block gets
      5 consistent gets
      0 physical reads
      0 redo size
     704 bytes sent via SQL*Net to client
     520 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      1 rows processed

    SQL> select * from emp_l where empno=&empno;
    输入 empno 的值: 2398
    原值  1: select * from emp_l where empno=&empno
    新值  1: select * from emp_l where empno=2398


    执行计划
    ----------------------------------------------------------
    Plan hash value: 733975378

    ------------------------------------------------------------------------------------------
    | Id | Operation    | Name  | Rows | Bytes | Cost (%CPU)| Time  |
    ------------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT   |   |   1 |  34 |   4  (0)| 00:00:01 |
    |  1 | TABLE ACCESS BY INDEX ROWID| emp_l |   1 |  34 |   4  (0)| 00:00:01 |
    |* 2 |  INDEX RANGE SCAN   | BT_NORMAL |   1 |  |   3  (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

     2 - access("EMPNO"=2398)


    统计信息
    ----------------------------------------------------------
      1 recursive calls
      0 db block gets
      5 consistent gets
      0 physical reads
      0 redo size
     703 bytes sent via SQL*Net to client
     520 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      1 rows processed
    总结如下:
    BITMAP EMPNO B-TREE
    Consistent Reads Physical Reads Consistent Reads Physical Reads
    5 0 1000 5 0
    5 0 2398 5 0
    5 0 8545 5 0
    5 0 98008 5 0
    5 0 85342 5 0
    5 0 128444 5 0
    5 0 858 5 0
    对emp_h表进行实验,得出的结果与之类似,这里不再獒述。从这里可以看出,在唯一列上的等值查询,位图索引与btree索引的效率相当。

    下面,我们在针对范围查询来进行测试。

    SQL> create bitmap index bm_random on emp_h(empno);

    索引已创建。

    SQL> analyze table emp_h compute statistics for table for all indexes for all columns;

    表已分析。

    SQL> select index_name,clustering_factor from user_indexes;

    INDEX_NAME      CLUSTERING_FACTOR
    ------------------------------ -----------------
    BM_RANDOM     1000000


    SQL> set autot traceonly
    SQL> select * from emp_h where empno between &range1 and &range2;
    输入 range1 的值: 1
    输入 range2 的值: 2300
    原值  1: select * from emp_h where empno between &range1 and &range2
    新值  1: select * from emp_h where empno between 1 and 2300

    已选择2300行。


    执行计划
    ----------------------------------------------------------
    Plan hash value: 811843605

    -------------------------------------------------------------------------------------------
    | Id | Operation     | Name  | Rows | Bytes | Cost (%CPU)| Time  |
    -------------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT    |   | 2299 | 85063 |  418  (1)| 00:00:06 |
    |  1 | TABLE ACCESS BY INDEX ROWID | emp_h | 2299 | 85063 |  418  (1)| 00:00:06 |
    |  2 |  BITMAP CONVERSION TO ROWIDS|   |  |  |     |  |
    |* 3 |  BITMAP INDEX RANGE SCAN  | BM_RANDOM |  |  |     |  |
    -------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

     3 - access("EMPNO">=1 AND "EMPNO"<=2300)


    统计信息
    ----------------------------------------------------------
      1 recursive calls
      0 db block gets
       2463 consistent gets
      0 physical reads
      0 redo size
      130225 bytes sent via SQL*Net to client
       2203 bytes received via SQL*Net from client
     155 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
       2300 rows processed

    SQL> select * from emp_h where empno between &range1 and &range2;
    输入 range1 的值: 8
    输入 range2 的值: 1980
    原值  1: select * from emp_h where empno between &range1 and &range2
    新值  1: select * from emp_h where empno between 8 and 1980

    已选择1973行。


    执行计划
    ----------------------------------------------------------
    Plan hash value: 811843605

    -------------------------------------------------------------------------------------------
    | Id | Operation     | Name  | Rows | Bytes | Cost (%CPU)| Time  |
    -------------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT    |   | 1972 | 72964 |  366  (0)| 00:00:05 |
    |  1 | TABLE ACCESS BY INDEX ROWID | emp_h | 1972 | 72964 |  366  (0)| 00:00:05 |
    |  2 |  BITMAP CONVERSION TO ROWIDS|   |  |  |     |  |
    |* 3 |  BITMAP INDEX RANGE SCAN  | BM_RANDOM |  |  |     |  |
    -------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

     3 - access("EMPNO">=8 AND "EMPNO"<=1980)


    统计信息
    ----------------------------------------------------------
      1 recursive calls
      0 db block gets
       2114 consistent gets
      0 physical reads
      0 redo size
      111758 bytes sent via SQL*Net to client
       1961 bytes received via SQL*Net from client
     133 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
       1973 rows processed

    SQL> select * from emp_h where empno between &range1 and &range2;
    输入 range1 的值: 28888
    输入 range2 的值: 31850
    原值  1: select * from emp_h where empno between &range1 and &range2
    新值  1: select * from emp_h where empno between 28888 and 31850

    已选择2963行。


    执行计划
    ----------------------------------------------------------
    Plan hash value: 811843605

    -------------------------------------------------------------------------------------------
    | Id | Operation     | Name  | Rows | Bytes | Cost (%CPU)| Time  |
    -------------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT    |   | 2962 |  107K|  513  (0)| 00:00:07 |
    |  1 | TABLE ACCESS BY INDEX ROWID | emp_h | 2962 |  107K|  513  (0)| 00:00:07 |
    |  2 |  BITMAP CONVERSION TO ROWIDS|   |  |  |     |  |
    |* 3 |  BITMAP INDEX RANGE SCAN  | BM_RANDOM |  |  |     |  |
    -------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

     3 - access("EMPNO">=28888 AND "EMPNO"<=31850)


    统计信息
    ----------------------------------------------------------
      1 recursive calls
      0 db block gets
       3172 consistent gets
      0 physical reads
      0 redo size
      170625 bytes sent via SQL*Net to client
       2687 bytes received via SQL*Net from client
     199 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
       2963 rows processed

    SQL> drop index bm_random;

    索引已删除。

    SQL> create index bt_random on emp_h(empno);

    索引已创建。

    SQL> analyze table emp_h compute statistics for table for all indexes for all columns;

    表已分析。

    SQL> set autot off
    SQL> select index_name,clustering_factor from user_indexes;

    INDEX_NAME      CLUSTERING_FACTOR
    ------------------------------ -----------------
    BT_RANDOM     999834
    SQL> set autot traceonly
    SQL> select * from emp_h where empno between &range1 and &range2;
    输入 range1 的值: 1
    输入 range2 的值: 2300
    原值  1: select * from emp_h where empno between &range1 and &range2
    新值  1: select * from emp_h where empno between 1 and 2300

    已选择2300行。


    执行计划
    ----------------------------------------------------------
    Plan hash value: 731629521

    --------------------------------------------------------------------------------
    | Id | Operation  | Name    | Rows | Bytes | Cost (%CPU)| Time   |
    --------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT |     | 2299 | 85063 | 1735  (1)| 00:00:21 |
    |* 1 | TABLE ACCESS FULL| emp_h | 2299 | 85063 | 1735  (1)| 00:00:21 |
    --------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

     1 - filter("EMPNO"<=2300 AND "EMPNO">=1)


    统计信息
    ----------------------------------------------------------
      1 recursive calls
      0 db block gets
       6410 consistent gets
      0 physical reads
      0 redo size
      121081 bytes sent via SQL*Net to client
       2203 bytes received via SQL*Net from client
     155 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
       2300 rows processed

    SQL> select * from emp_h where empno between &range1 and &range2;
    输入 range1 的值: 8
    输入 range2 的值: 1980
    原值  1: select * from emp_h where empno between &range1 and &range2
    新值  1: select * from emp_h where empno between 8 and 1980

    已选择1973行。


    执行计划
    ----------------------------------------------------------
    Plan hash value: 731629521

    --------------------------------------------------------------------------------
    | Id | Operation  | Name    | Rows | Bytes | Cost (%CPU)| Time   |
    --------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT |     | 1972 | 72964 | 1735  (1)| 00:00:21 |
    |* 1 | TABLE ACCESS FULL| emp_h | 1972 | 72964 | 1735  (1)| 00:00:21 |
    --------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

     1 - filter("EMPNO"<=1980 AND "EMPNO">=8)


    统计信息
    ----------------------------------------------------------
      1 recursive calls
      0 db block gets
       6388 consistent gets
      0 physical reads
      0 redo size
      103922 bytes sent via SQL*Net to client
       1961 bytes received via SQL*Net from client
     133 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
       1973 rows processed

    归纳如下,
    BITMAP EMPNO (Range) B-TREE
    Consistent Reads Physical Reads Consistent Reads Physical Reads
    2463 0 1-2300 6410 0
    2114 0 8-1980 6388 0
    2572 0 1850-4250 6418 0
    3172 0 28888-31850 6456 0
    2762 0 82900-85478 6431 0
    7254 0 984888-1000000 7254 0
    从这里可以看出,位图索引要优于btree索引,这是因为btree索引的cluster factor 较大,从而优化器选择了全表扫描。即便在emp_l 表下,即clustering factor较小时,位图索引btree索引相当的。因此在distinct cardinality 较大的情况下,范围扫描的效率位图索引也是不逊色与B*索引。

    展开全文
  • oracle 位图索引原理

    2015-01-20 17:54:02
    1、语法 create bitmap index index_name on 表名(字段);...类似这样的数据,如果查询的时候用到 ,因为数据库中有很多这样的数据,所以一般的索引起不到查询加速的作用,而建立位图索引后会产生如
    1、语法
    create bitmap index index_name on 表名(字段);
    2、举个例子你就能明白了:
    如有表 test(id,name,address)
    数据
    (1,张三,大连)
    (2,李四,天津)
    (3,王五,北京)
    (4,赵六,大连)
    ....
    
    类似这样的数据,如果查询的时候用到 ,因为数据库中有很多这样的数据,所以一般的索引起不到查询加速的作用,而建立位图索引后会产生如下位图效果:
    假设有4条数据(就如上所示)
    
    大连 天津  北京
     1    0     0
     0    1     0
     0    0     1
     1    0     0
    
    这样当查询:
    select * from 表 where address='大连' or address='北京';
    的时候数据库很快就能根据 同行的 1和0 判断出那一条数据符合要求。
    
    
    位图索引节省空间的原理
    
    
    b-tree索引这样存高度重复的数据
    
    男
    男
    男
    男
    女
    女
    
    对高度重复的数据 普通索引要反复存,
    
    对于bitmap索引而言:
    
    男  111100 
    女  000011
    
    男和女的值只要存一次,而后面的位图只占几个字节, 要比btree 索引节约空间

    展开全文
  • oracle位图索引

    千次阅读 2017-12-01 16:17:55
    位图索引oracle查询过程中的一大利器,本章来学习一下oracle的基本原理和概念。假如有如下的一张表: 假如说用普通的查询: SELECT * FROM PERSON WHERE SEX=’男’; 那么数据库便要逐行的去查找性别为男的...

    位图索引是oracle查询过程中的一大利器,本章来学习一下oracle位图索引的基本原理和概念。假如有如下的一张表:
    这里写图片描述
    假如说用普通的查询:

    SELECT * FROM TUSER WHERE TSEX=’男’;

    那么数据库便要逐行的去查找性别为男的数据,当数据比较多的时候,这个查询就会变得很慢了。那怎么办呢?针对于这种查询,位图索引将会是一种非常好的办法。
    当我们使用位图索引的时候,从性别方向上将会有两个向量,男向量为101,女向量为010
    这里写图片描述
    而婚姻方向上则有三个向量,已婚为100,未婚为010,离婚为001
    这里写图片描述
    那么假如做如下的查询

    select * from tuser where tsex=’男’ and marital=’已婚’;

    这个时候用位图向量来查询就非常的简单了,直接对男和已婚两组向量做and操作,那么就会得到如下的结果:
    这里写图片描述
    得到的结果是100,即第一条数据符合要求,非常快速的就得到了结果。


    从上边的分析可以看以下下的两种场合非常不适合使用位图索引:

    1.列上有大量的特征值,比如身份证号,这样的数据列,每个人的都不相同,如果为这样的列添加位图索引会得不偿失。
    2.频繁更新的列,如果频繁的更新位图索引所在的列的数据,那么oracle就不得不频繁的维护位图索引,这样就极有可能导致表被锁,影响整个系统的性能。


    创建位图索引的方式

    CREATE BITMAP INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME);

    展开全文
  • Oracle位图索引

    千次阅读 2014-07-24 08:52:31
    什么是位图索引 我们目前大量使用的索引一般主要是B*Tree索引,在索引结构中存储着键值和键值的RowID,并且是一一对应的. 而位图索引主要针对大量相同值的列而创建(例如:类别,操作员,部门ID,库房ID等), 索引块的...
    一.什么是位图索引
    我们目前大量使用的索引一般主要是B*Tree索引,在索引结构中存储着键值和键值的RowID,并且是一一对应的.
    而位图索引主要针对大量相同值的列而创建(例如:类别,操作员,部门ID,库房ID等),
    索引块的一个索引行中存储键值和起止Rowid,以及这些键值的位置编码,
    位置编码中的每一位表示键值对应的数据行的有无.一个位图索引块可能指向的是几十甚至成百上千行数据的位置.

    这种方式存储数据,相对于B*Tree索引,占用的空间非常小,创建和使用非常快.

    当根据键值查询时,可以根据起始Rowid和位图状态,快速定位数据.
    当根据键值做and,or或 in(x,y,..)查询时,直接用索引的位图进行或运算,快速得出结果行数据.
    当select count(XX) 时,可以直接访问索引就快速得出统计数据.

    创建语法很简单,就是在普通索引创建的语法中index前加关键字bitmap即可,例如:
    create bitmap index H病人挂号记录_ix_执行人 on H病人挂号记录(执行人);

    二.位图索引的特点
    1.Bitmap索引的存储空间
    相对于B*Tree索引,位图索引由于只存储键值的起止Rowid和位图,占用的空间非常少.
    bitmap的空间占用主要根以下4个因素相关:
    a.表的总记录数
    b.索引列的键值多少,列的不同值越少,所需的位图就越少.
    c.操作的类型,批量插入比单条插入所面的位图要少得多,8i,9i下是这样的,10G则没有这种区别,详见后面的分析.
    d.索引列相同键值的物理分布,8i,9i中,不同块上的数据,相同的键值,会建立不同的位图行(段)来表示

    注:本文提到的8i,9i,10g,我试验的环境是8.1.7,9.2.0.5,10.2

    2.Bitmap索引创建的速度
    位图索引创建时不需要排序,并且按位存储,所需的空间也少.
    B*Tree索引则在创建时需要排序,定位等操作,速度要慢得多.

    3.Bitmap索引允许键值为空
    B*Tree索引由于不记录空值,当基于is null的查询时,会使用全表扫描,
    而对位图索引列进行is null查询时,则可以使用索引.

    4.Bitmap索引对表记录的高效访问
    当使用count(XX),可以直接访问索引就快速得出统计数据.
    当根据位图索引的列进行and,or或 in(x,y,..)查询时,直接用索引的位图进行或运算,在访问数据之前可事先过滤数据.

    5.Bitmap索引对批量DML操作只需进行一次索引
    由于通过位图反映数据情况,批量操作时对索引的更新速度比B*Tree索引一行一行的处理快得多.

    6.Bitmap索引的锁机制
    对于B*Tree索引,insert操作不会锁定其它会话的DML操作.
    而位图索引,由于用位图反映数据,不同会话更新相同键值的同一位图段,insert、update、delete相互操作都会发锁定。

    对于oracle 8i,9i,单行插入时,由于一个位图行(位图段)只记录8行记录,所以最多锁住相同键值的8行数据的DML操作.
    而批量插入时,和10G一样,同一键值只有一个位图行(位图段),所以,相同键值的所有数据的DML操作都会被锁住。

    下面,针对8i,9i观察一下锁机制:
    SQL> Declare
    Begin
    For i In 1..9
    Loop
       Insert Into H病人挂号记录(Id,No,号别,执行人) Values(i,'G000001',1,'张1');
    End Loop;
    Commit;
    End;
    /
    SQL> delete H病人挂号记录 where id=1;
    不提交,另开一个会话,
    SQL> delete H病人挂号记录 where id=9;
    操作可以进行,没有锁定。
    SQL> delete H病人挂号记录 where id=8;
    操作等待,由于和另外一个会话操作的记录的位图索引在同一个位图段上(一个位图段最多8行),所以被锁住了。


    三.位图索引的适用场合
    1.位图索引是Oracle数据库在7.3版本中加入的,8i,9i企业版和个人版支持,标准版不支持.
    2.基于规则的优化器无法使用Bitmap索引
    3.适应于有大量重复值的列查询
    4.对于8i,9i版本,不适用于单行插入,适用于批量插入的数据,
       因为单行插入时,相同键值,每插入8行就会生成一行索引块中的位图段,即使相同的值.
       而批量插入时,相同键值只生成一个位图段.
    5.由于并发DML操作锁定的是整个位图段的大量数据行,所以位图索引主要是用于OLAP应用,也可以用于OLTP中主要为读操作的表.


    关于bitmap的两个参数
    SQL> show parameter bitmap;

    NAME                                  TYPE         VALUE
    ------------------------------------ ----------- ------------------------------
    bitmap_merge_area_size                integer      1048576
    create_bitmap_area_size               integer      8388608

    其中bitmap_merge_area_size是bitmap索引进行合并操作时使用的内存区域,create_bitmap_area_size是创建时使用的内存区域.
    8i,9i中,需要根据bitmap的大小以及常见的使用情况来调整.
    9i以上,只需设置pga_aggregate_target的值,Oracle即会自动进和内存的调整.


    四.位图索引存储原理
    位图索引对数据表的列的每一个键值分别存储为一个位图,Oracle对于不同的版本,不同的操作方式,数据生成均有差别.
    对于8i,9i,
    下面分3种方式来讨论数据的插入:
    a.一次插入一行,插入多行后,一次提交;
    b.每插入一行,提交一次;
    c.批量插入方式,一次提交;

    对于第一种方式,观察位图索引的变化情况.
    a.假设插入8行相同键值的数据,如果以每行方式插入,然后一次提交,则会生成8个位图
    SQL> Insert Into H病人挂号记录(Id,No,号别,执行人) Values(1,'G000001',1,'张1');
    1 row inserted
    SQL> /
    1 row inserted
    SQL> /
    1 row inserted
    SQL> /
    1 row inserted
    SQL> /
    1 row inserted
    SQL> /
    1 row inserted
    SQL> /
    1 row inserted
    SQL> /
    1 row inserted
    SQL> commit;
    Commit complete

    SQL> alter system dump datafile 1 block 40028;
    System altered

    row#0[7847] flag: -----, lock: 0
    col 0; len 3; (3):   d5 c5 31    --键值'张1'
    col 1; len 6; (6):   00 40 9c 54 00 00   --rowid的起始位置
    col 2; len 6; (6):   00 40 9c 54 00 07   --rowid的终止位置
    col 3; len 2; (2):   c8 ff    --位图编码
    row#1[7802] flag: -----, lock: 0
    col 0; len 3; (3):   d5 c5 31
    col 1; len 6; (6):   00 40 9c 54 00 08
    col 2; len 6; (6):   00 40 9c 54 00 0f
    col 3; len 2; (2):   c8 03
    row#2[7780] flag: -----, lock: 0
    col 0; len 3; (3):   d5 c5 32
    col 1; len 6; (6):   00 40 9c 54 00 08
    col 2; len 6; (6):   00 40 9c 54 00 0f
    col 3; len 1; (1):   02
    row#3[7758] flag: -----, lock: 0
    col 0; len 3; (3):   d5 c5 33
    col 1; len 6; (6):   00 40 9c 54 00 08
    col 2; len 6; (6):   00 40 9c 54 00 0f
    col 3; len 1; (1):   03
    row#4[7736] flag: -----, lock: 2
    col 0; len 3; (3):   d5 c5 34
    col 1; len 6; (6):   00 40 9c 54 00 08
    col 2; len 6; (6):   00 40 9c 54 00 0f
    col 3; len 1; (1):   04
    row#5[7714] flag: -----, lock: 2
    col 0; len 3; (3):   d5 c5 35
    col 1; len 6; (6):   00 40 9c 54 00 08
    col 2; len 6; (6):   00 40 9c 54 00 0f
    col 3; len 1; (1):   05
    ----- end of leaf block dump -----


    但是,下次再插入一行相同键值的数据时,会自动合并这8行位图为一行位图,并生成一个新的索引位图行存放刚插入行的索引:
    SQL> Insert Into H病人挂号记录(Id,No,号别,执行人) Values(1,'G000001',1,'张1');
    1 row inserted
    SQL> commit;
    Commit complete
    SQL> alter system dump datafile 1 block 40028;
    System altered

    row#0[7847] flag: -----, lock: 2
    col 0; len 3; (3):   d5 c5 31
    col 1; len 6; (6):   00 40 9c 54 00 00
    col 2; len 6; (6):   00 40 9c 54 00 07
    col 3; len 2; (2):   c8 ff
    row#1[7825] flag: -----, lock: 2
    col 0; len 3; (3):   d5 c5 31
    col 1; len 6; (6):   00 40 9c 54 00 08
    col 2; len 6; (6):   00 40 9c 54 00 0f
    col 3; len 1; (1):   00
    ----- end of leaf block dump -----

    b.数据每行提交方式,与上面的情况相似,但有一点不一样,每提交一行,拷贝原来的位图,生成新的位图,并标记原来的位图为已删除,
    标记为已删除的位图,只有索引块需要分配新的位图时,才会清除标记为已删除的位图,重用这些空间.

    在8i,9i上实验的结果,与ITPUB的<Oracle 数据库性能优化>一书378页一致.
    如果1000条相同键值的数据插入,将生成125个包括8条记录的位图行.

    c.第三种方式,批量插入数据,insert into H病人挂号记录(Id,No,号别,执行人) select ***方式,
       同一键值,只生成一次位图,只有一个位图.

    SQL> Insert Into H病人挂号记录(Id,No,号别,执行人)
    Select 1,'G000001',1,'张1' From dual
    Union All
    Select 2,'G000002',1,'张1' From dual
    Union All
    Select 3,'G000003',1,'张1' From dual
    Union All
    Select 4,'G000004',1,'张1' From dual
    Union All
    Select 5,'G000005',1,'张1' From dual
    Union All
    Select 6,'G000006',1,'张1' From dual
    Union All
    Select 7,'G000006',1,'张1' From dual
    Union All
    Select 8,'G000006',1,'张1' From dual
    Union All
    Select 9,'G000006',1,'张1' From dual;
    SQL> commit;
    Commit complete
    SQL> alter system dump datafile 1 block 40028;
    System altered

    row#0[8006] flag: -----, lock: 2
    col 0; len 3; (3):   d5 c5 31
    col 1; len 6; (6):   00 40 9c 54 00 00
    col 2; len 6; (6):   00 40 9c 54 00 0f
    col 3; len 3; (3):   c9 ff 01
    row#1[8030] flag: ---D-, lock: 2
    col 0; NULL
    col 1; NULL
    col 2; NULL
    col 3; NULL
    ----- end of leaf block dump -----

    所以,位图索引最好采用批量插入方式,这样,每个键值只生成一个位图.而单行数据插入方式,每个键值将每8行数据生成一个位图.


    10G的情况,则简单得多.
    上面3种方式,相同键值的插入,位图的生成是一样的,只有一个位图,并且,每次提交时,并不会删除以前的位图,而是直接修改对应键值的位图.

    每次插入一行数据,插入9行后提交
    row#0[7763] flag: ------, lock: 2, len=29
    col 0; len 3; (3):   d5 c5 31
    col 1; len 6; (6):   00 00 00 00 00 00
    col 2; len 6; (6):   00 40 ef f2 00 0f
    col 3; len 8; (8):   f9 e4 d5 dc bc 01 ff 01
    ----- end of leaf block dump -----

    再批量插入9行数据并提交
    row#0[7733] flag: ------, lock: 2, len=30
    col 0; len 3; (3):   d5 c5 31
    col 1; len 6; (6):   00 00 00 00 00 00
    col 2; len 6; (6):   00 40 ef f2 00 17
    col 3; len 9; (9):   fa e4 d5 dc bc 01 ff ff 03
    ----- end of leaf block dump -----

    可以看出,10G对位图索引的存储进行了优化,一个键值在索引块中只有一个位图

    关于位图索引的一些信息,可以参考:bitmap 的一点探究 http://www.itpub.net/114023.html
    注意,其中有些结论并不是完全正确的,可以自己实验证明,另外,该文涉及的实验没有标明Oracle版本,不同的版本,结果有差异.
    展开全文
  • 位图索引是从oracle 7.3版本开始引入的。目前oracle企业版和个人版都支持位图索引,但是标准版不支持。位图索引是这样一种结构,其中用一个索引键条目存储指向多行的指针,这与B*树结构不同,在b*树结构中,索引键...
  • [Oracle] 位图索引

    千次阅读 2014-04-18 17:35:43
    位图索引原理 位图索引的优势 1、快速统计条数 由于位图索引只存储0和1的指,因此它的空间占用很小,向count(*)之类的操作用位图索引就很快。 2、高效即席查询 我们在OLAP系统中,经常会做多维度的统计,如...
  • Oracle位图索引,sql,sql教程,Oracle基础 Oracle的索引主要包含两类:BTree和位图索引。默认情况下大多使用Btree索引,该索引就是通常所见 唯一索引、聚簇索引等等,Btree用在...
  • 探讨Oracle位图索引

    2011-03-22 18:07:00
    Oracle的索引主要包含两类:BTree和位图索引。默认情况下大多使用Btree索引,该索引就是通常所见 唯一索引、聚簇索引等等,Btree用在OLTP,加快查询速度。...位图索引的基本原理是在索引中使用位图而不
  • Oracle位图索引简述

    2011-11-01 13:45:57
    Oracle的索引主要包含两类:BTree和位图索引。默认情况下大多使用Btree索引,该索引就是通常所见 唯一索引、聚簇索引等等,Btree用在OLTP,加快查询速度。位图索引Oracle的比较引人注目的地方,其主要用在...
  • oracle-索引原理

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

    千次阅读 2005-04-23 11:51:00
    Oracle的索引主要包含两类:BTree和位图索引。默认情况下大多使用Btree索引,该索引就是通常所见 唯一索引、聚簇索引等等,Btree用在OLTP,加快查询速度。位图索引Oracle的比较引人注目的地方,其主要用在OLAP...
  • 位图索引:原理(BitMap index)

    万次阅读 2018-05-15 11:09:12
    位图(BitMap)索引 前段时间听同事分享,偶尔讲起Oracle数据库的位图索引,顿时大感兴趣。说来惭愧,在这之前对位图索引一无所知,因此趁此机会写篇博文介绍下位图索引。1. 案例 有张表名为table的表,由三列组成...
  • oracle中的位图索引

    2018-03-15 10:41:55
    要为这些基数值比较小的列建索引,就需要建立位图索引位图索引适合静态数据,而不适合索引频繁更新的列。这个原因是因为在更新该列时,需要同时更新系统中的该列该值对应位图向量,此时,系统会将所有该列为该值...
  • 位图索引存储原理 位图索引对数据表的列的每一个键值分别存储为一个位图,Oracle对于不同的版本,不同的操作方式,数据生成均有差别. 对于8i,9i, 下面分3种方式来讨论数据的插入: a.一次插入一行,插入多行后,一次...
  • 通过两个图形说明了在oracle数据库中b-tree索引和位图索引的工作原理
  • Oracle 索引原理和种类

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

    千次阅读 2006-06-02 11:46:00
    [1] 基本的索引概念查询DBA_INDEXES视图可得到表中所有索引的列表,注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。...在 Oracle9i引入跳跃式扫描的索引访问方法之前,查询只能在有限条件下
  • ---测试位图索引重复度前准备工作 drop table t purge; set autotrace off create table t as select * from dba_objects; insert into t select * from t; insert into t select * from t; insert into t ...
  • 位图索引:原理(BitMap index)——浅显易懂
  • oracle索引原理

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

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

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

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 3,696
精华内容 1,478
关键字:

oracle位图索引原理