精华内容
下载资源
问答
  • 创建位图索引

    2017-05-28 10:01:00
    创建位图索引 位图索引 1位图索引与B树索引不同,位图索引不存储rowid值(数据实际物理地址),也不存储键值。 2在特殊的列上创建位图 索引. 3特殊的列是指该列的基数很低的列(基数:列值的数量比列的行数来的...

    创建位图索引
    位图索引
    1位图索引与B树索引不同,位图索引不存储rowid值(数据实际物理地址),也不存储键值。
    2在特殊的列上创建位图 索引.

    3特殊的列是指该列的基数很低的列(基数:列值的数量比列的行数来的小)。
    举例:性别列的取值只有2个(男女),但是性别列的行数远远大于该列的取值。

    4基数很低的列不适合B树索引,适合位图索引,oracle建议,当一个列的所有取值数量与该列的行数比小于1%时,该列不适合建立B树索引,适合位图索引。

    5在表中放置单独的位图索引是没有意义的,只有多个列建立位图索引,系统才能有效的利用位图索引提高查询速度.

    6因为位图索引不能是唯一索引,也不能对其进行键压缩

    7位图索引的作用源于与其他位图索引的结合,当位图索引的多个列进行查询时,oracle对这些上的位图索引进行布尔and和or运行,最终返回结果.

    性别列位图索引以及对应的表行概念示意图
    这里写图片描述
    具体实现
    1 oracle对employee进行全表扫描
    2创建位图索引,并构建一个表(右边)
    3右边表的数据与左边表性别列数据一一对应,(数值1代表存在,0代表不存在)
    4如果给定右表起始物理地址和终止物理地址,那么对应左表性别列上的值的实际物理地址还是可以取到的。

    性别列B树索引结构图
    这里写图片描述
    (性别列不适合建立B数索引)

    创建位图索引
    /位图索引/
    create bitmap index ssex_bitmap_index on zhou.student(ssex)
    tablespace MYSPACE;
    这里写图片描述

    注意:
    1在表中放置单独的位图索引是没有意义的,只有多个列建立位图索引,系统才能有效的利用位图索引提高查询速度.
    2因为位图索引不能是唯一索引,也不能对其进行键压缩
    3位图索引的作用源于与其他位图索引的结合,当位图索引的多个列进行查询时,oracle对这些上的位图索引进行布尔and和or运行,最终返回结果.

    转载于:https://www.cnblogs.com/feiZhou/p/9344290.html

    展开全文
  • oracle 位图索引

    2019-01-16 13:34:00
    主要针对大量相同值的列而创建,比如(性别、婚配等字段可选值很少的字段创建位图索引); 位图索引非常适合于决策支持系统(Decision Support System,DSS)和数据仓库,它们不应该用于通过事务处理应用程序访问的表...
    位图索引是一种使用位图的特殊数据库索引。主要针对大量相同值的列而创建,比如(性别、婚配等字段可选值很少的字段创建位图索引);

    位图索引非常适合于决策支持系统(Decision Support System,DSS)和数据仓库,它们不应该用于通过事务处理应用程序访问的表。它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表;

    索引块的一个索引行中存储键值和起止Rowid,以及这些键值的位置编码,

    位置编码中的每一位表示键值对应的数据行的有无.一个块可能指向的是几十甚至成百上千行数据的位置.
    这种方式存储数据,相对于B*Tree索引,占用的空间非常小,创建和使用非常快
    当根据键值查询时,可以根据起始Rowid和位图状态,快速定位数据.
    创建语法很简单,就是在普通索引创建的语法中index前加关键字bitmap即可,例如:
     
    位图索引的特点:
    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相互操作都会发锁定。
     
     
     

    技巧:

    对于有较低基数的列需要使用位图索引。性别列就是这样一个例子,它有两个可能值:男或女(基数仅为2)。位图对于低基数(少量的不同值)列来说非常快,这是因为索引的尺寸相对于B树索引来说小了很多。因为这些索引是低基数的B树索引,

    所以非常小,因此您可以经常检索表中超过半数的行,并且仍使用位图索引。

    当大多数条目不会向位图添加新的值时,位图索引在批处理(单用户)操作中加载表(插入操作)方面通常要比B树做得好。当多个会话同时向表中插入行时不应该使用位图索引,在大多数事务处理应用程序中都会发生这种情况。

    技巧:

    如果要查询位图索引列表,可以在USER _INDEXES视图中查询index_type列。

    建议不要在一些联机事务处理(OLTP)应用程序中使用位图索引。B树索引的索引值中包含ROWID,这样Oracle就可以在行级别上锁定索引。

    位图索引存储为压缩的索引值,其中包含了一定范围的ROWID,因此Oracle必须针对一个给定值锁定所有范围内的ROWID。这种锁定类型可能在某些DML语句中造成死锁。SELECT语句不会受到这种锁定问题的影响。

    位图索引有很多限制,如下所示:

      • 基于规则的优化器不会考虑位图索引。
      • 当执行ALTER TABLE语句并修改包含有位图索引的列时,会使位图索引失效。
      • 位图索引不包含任何列数据,并且不能用于任何类型的完整性检查。
      • 位图索引不能被声明为唯一索引。
      • 位图索引的最大长度为30。

    技巧:

    不要在繁重的OLTP环境中使用位图索引

     

    转载于:https://www.cnblogs.com/leewi/p/10276537.html

    展开全文
  • Oracle 位图索引

    2014-07-20 11:12:00
    内容简介: 1.位图索引 ...2.1创建位图连接索引的注意事项: 1.位图索引: 1.1位图索引使用注意事项: ❏ 一般适用于低基数列; ❏适合数据仓库; ❏对于启用位图索引的表,应尽量减少或避免DML操作; ❏...

    内容简介:

    1.位图索引

    1.1位图索引使用注意事项;

    1.2 使用位图索引;

    1.3 位图索引对DML操作的影响;

    2.位图连接索引

    2.1 明确需求后使用位图索引;

    2.1创建位图连接索引的注意事项:

     

    1.位图索引:

    1.1位图索引使用注意事项:

    ❏ 一般适用于低基数列;

     适合数据仓库;

     对于启用位图索引的表,应尽量减少或避免DML操作;

     如果对一张含有多列位图索引的表进行大量DML操作,应考虑将位图索引删除,DML操作结束后重建位图索引;

     不适用于频繁持续发生DML操作的OLTP系统,会出现行锁定,阻碍更新性能;


    1.2 使用位图索引

    位图索引与B-TREE索引有很大的不同,一个位图索引由多个位串组成,每个位串都表示基础列中一个独立的有效值;每个位串是打开或关闭,表示该值是否用于某一行;人员信息表th03 为例,性别(gender)字段的值(男、女、未记录),假如为其创建位图索引,那么每个位串(男、女、未记录)中的单个位表示一个给定行的值是男、女还是未记录;当判断某一列是否适合创建位图索引时,需要考虑是否符合”低基数列”,根据应用程序、数据组成以及数据库中的表的情况不同,是否创建位图索引的结论也可能不同;通常用来判断的一条基本经验法则是:”如果该列的有效值数目不足表中行数的1%,那么它就适合创建位图索引,th03来说,表行数为:500万行,而性别列的有效值数目仅为3( 男、女、未记录),可以确定它适合创建位图索引:

    人员信息表(th03)

     

    ID

    NAME

    GENDER

    IDCARD

    HOMEADDR

    JOBNO

    BIRTHDATE

    1

    998698

    李天

    440623197007253619

    水晶洞1

    526456

    25-JUL-70

    2

    998699

    李花

    510802197007251223

    水晶洞2

    5785452

    25-JUL-70

    3

    584625

    李某

    未记录

    564551545265642155

    水晶洞3

    1565452

    01-JUL-88

     

    SQL> create bitmap index ind_th03_gender on th03(gender) tablespace tbs03;

    Index created.

    Elapsed: 00:00:01.05

    SQL> execute dbms_stats.gather_table_stats('sywu','th03',cascade=>true);

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:04.31

    SQL> @/oracle/getind

    TABLE_NAME INDEX_NAME COLUMN_NAME   SIZE_GB INDEX_TY STATUS COMPRESS NUM_ROWS DISTINCT_KEYS

    ------------------------------ ------------------------------ ------------------------------ ---------- -------- -------- --------- ---------- -------- ---------- ----

    TH03 IND_TH03_GENDER GENDER   .001953125 BITMAP VALID DISABLED 453 3

    位图索引的创建非常快并且占用的空间也非常小;位图索引和B-TREE索引存储值的方式不同,它存储表中的每一行值(包括空值),对于B-TREE索引,单列索引不存储空值,复合索引只要有一个非空值就可以存储;所以当执行 IS NULL 或者 IS NOT NULL 查询时位图索引的效率要高于B-TREE索引:

    SQL> select count(*) from th03 where gender is null;

    -----------------------------------------------------------------------------------------------

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

    -----------------------------------------------------------------------------------------------

    | 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |

    | 1 | SORT AGGREGATE | | 1 | 5 | | |

    | 2 | BITMAP CONVERSION COUNT | | 1 | 5 | 1 (0)| 00:00:01 |

    |* 3 | BITMAP INDEX SINGLE VALUE| IND_TH03_GENDER | | | | |

    3 - access("GENDER" IS NULL)

    Statistics

    ----------------------------------------------------------

    1 recursive calls

    0 db block gets

    2 consistent gets

    1 physical reads

    如果在相同表相同列建立B-TREE索引,则该执行必须全表扫描:

    SQL> select count(*) from th03 where gender is null;

    ---------------------------------------------------------------------------

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

    ---------------------------------------------------------------------------

    | 0 | SELECT STATEMENT | | 1 | 5 | 14908 (1)| 00:02:59 |

    | 1 | SORT AGGREGATE | | 1 | 5 | | |

    |* 2 | TABLE ACCESS FULL| TH03 | 1 | 5 | 14908 (1)| 00:02:59 |

    ---------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

    2 - filter("GENDER" IS NULL)

    Statistics

    ----------------------------------------------------------

    1 recursive calls

    0 db block gets

    54574 consistent gets

    54562 physical reads

    位图索引还可以在另外一些情况使用,如使用聚合函数:

    SQL> select count(*),count(gender) from th03;

    COUNT(*) COUNT(GENDER)

    ---------- -------------

    5000000 5000000

    -------------------------------------------------------------------------------------------------

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

    -------------------------------------------------------------------------------------------------

    | 0 | SELECT STATEMENT | | 1 | 5 | 203 (0)| 00:00:03 |

    | 1 | SORT AGGREGATE | | 1 | 5 | | |

    | 2 | BITMAP CONVERSION TO ROWIDS | | 5000K| 23M| 203 (0)| 00:00:03 |

    | 3 | BITMAP INDEX FAST FULL SCAN| IND_TH03_GENDER | | | | |

    -------------------------------------------------------------------------------------------------

    Statistics

    ----------------------------------------------------------

    0 recursive calls

    0 db block gets

    232 consistent gets

    0 physical reads

    如果你决定在应用程序中实现位图索引,应时不时的检查建立了位图索引的列的数据组成,这一点很重要,如果你算错了包含位图索引的任何列的基数,可能会对应用程序造成负面影响,(位图索引的存储空间会增加、查询性能会降低、重建索引的时间将增加);

    1.3 位图索引对DML操作的影响:

    为了便于测试,我创建另一张分区表 th04,并为th04的性别、出生日期列创建位图索引,最后向它插入100万行数据:

    SQL> create table th04

    partition by range(id)(

    partition th04_part1 values less than(1000000) tablespace tbs03,

    partition th04_part2 values less than(2000000) tablespace tbs03,

    partition th04_part3 values less than(3000000) tablespace tbs03,

    partition th04_part4 values less than(4000000) tablespace tbs03,

    partition th04_part5 values less than(5000000) tablespace tbs03,

    partition th04_part6 values less than(maxvalue) tablespace tbs03

    ) as select * from tbbase where 1=0 ;

    SQL> create bitmap index ind_th04_gender on th04(gender) local tablespace tbs03;

    SQL> create bitmap index ind_th04_birthdate on th04(birthdate) local tablespace tbs03;

    SQL>insert into th04 select * from tbbase where rownum<1000001;

    1000000 rows created.

    Elapsed: 00:00:40.24

    从结果可以看出,插入100万行的数据花费40,从表面上看花费的时间似乎是合理的,但当数据量不断增加时,特别对于一个数据仓库环境,一天处理几百万甚至亿行数据是司空见惯的,合理的情况应考虑删除位图索引(如果是分区表则将目标分区标记为不可用),执行完DML加载操作后重建位图索引;

    SQL> alter index IND_TH04_GENDER unusable;

    SQL> alter index IND_TH04_BIRTHDATE unusable;

    SQL> insert into th04 select * from tbbase where rownum<1000001;

    禁用位图索引后,插入100万行数据只花费23 ,对于大数据而言这或许可以提高装载数据的性能;数据装载结束后重建位图索引:

    SQL> alter index ind_th04_gender rebuild partition TH04_PART1;

    alter index ind_th04_gender rebuild partition TH04_PART2;

    alter index ind_th04_gender rebuild partition TH04_PART3;

    alter index ind_th04_gender rebuild partition TH04_PART4;

    alter index ind_th04_gender rebuild partition TH04_PART5;

    alter index ind_th04_gender rebuild partition TH04_PART6;

    alter index ind_th04_birthdate rebuild partition th04_part1;

    .........

    重建分区索引也可以通过下面的命令重建:

    SQL> alter table th04 modify partition th04_part1 rebuild unusable local indexes;

    此命令虽然简单,但它也有不足之处,对于一个指定的分区,它只能按顺序执行;而对于每一个指令发出的命令重建分区,它可以同时执行多个语句,实现并行重建索引;

    除装载数据的影响外,位图索引也会影响数据的DML操作,请观察下面的人员信息表数据:

    人员信息表(th04)

     

    ID

    NAME

    GENDER

    IDCARD

    HOMEADDR

    JOBNO

    BIRTHDATE

    1

    998698

    李天

    440623197007253619

    水晶洞1

    526456

    25-JUL-70

    2

    998699

    李四

    510802197007251223

    水晶洞2

    5785452

    25-JUL-70

    .....

    ....

     

     

     

     

     

     

     

    数据显示他们的出生日期是相同的,并且出生日期列(BIRTHDATE)还建立了位图索引( IND_TH04_BIRTHDATE) ,因业务错误记录两人的出生日期,so,现在对其修改:

    ---session 1-----

    SQL> select distinct sid from v$mystat;

    SID

    ----------

    191

    SQL> update th04 set birthdate='26-JUL-70' where idcard='440623197007253619';

    1 row updated.

    此时session 1 的用户因为业务繁忙没有及时提交,这时另一个业务员在新的会话 session2中修该另一个错误记录:

    ----session 2 -----

    SQL> select distinct sid from v$mystat;

    SID

    ----------

    194

    SQL> update th04 set birthdate='27-JUL-70' where idcard='510802197007251223';

    session 2 中的用户会一直处于等待状态,因为他们修改的错误人员出生日期在更新之前在同一个位图索引位串中,当修改位串中的位信息时位串会被锁定,直到更新提交后更新位串中的位值;观察此时的锁状态:

    SQL>select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in(191,194) order by sid

    SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

    ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

    191 AE 100 0 4 0 2052 0

    191 TM 75667 0 3 0 263 0

    191 TX 65562 848 6 0 263 1

    191 TM 75668 0 3 0 263 0

    191 TO 65927 1 3 0 286 0

    194 TM 75668 0 3 0 238 0

    194 TX 131081 1071 6 0 238 0

    194 TM 75667 0 3 0 238 0

    194 TX 65562 848 0 4 238 0

    194 AE 100 0 4 0 1406 0

    对于session 1(191)此时持有一个6级事务锁,并且堵塞session 2(194),它们请求的资源是一样的,这并非巧合;只有当session 1(191)提交或回退后,这个6级事物锁才会被释放,session 2(194)才能持有锁修改数据;

    SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

    ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

    191 AE 100 0 4 0 2832 0

    191 TO 65927 1 3 0 1066 0

    194 TX 131081 1071 6 0 1018 0

    194 TM 75667 0 3 0 1018 0

    194 TM 75668 0 3 0 1018 0

    194 AE 100 0 4 0 2186 0

    所以建立位图索引时,应仔细分析表结构和表数据,作出明智、合理选择;以上测试因环境、版本、数据库状态测试结果可能不同;

     

    转载于:https://www.cnblogs.com/zwl715/p/3855990.html

    展开全文
  • ORACLE位图索引

    2013-12-28 22:16:00
    内容简介: ...2.1创建位图连接索引的注意事项: 1.位图索引: 1.1位图索引使用注意事项: ❏ 一般适用于低基数列; ❏ 适合数据仓库; ❏ 对于启用位图索引的表,应尽量减少或避免DML操作; ❏ 如果对...

    内容简介:

    1.位图索引

    1.1位图索引使用注意事项;

    1.2 使用位图索引;

    1.3 位图索引对DML操作的影响;

    2.位图连接索引

    2.1 明确需求后使用位图索引;

    2.1创建位图连接索引的注意事项:

     

    1.位图索引:

    1.1位图索引使用注意事项:

    ❏ 一般适用于低基数列;

    适合数据仓库;

    对于启用位图索引的表,应尽量减少或避免DML操作;

    如果对一张含有多列位图索引的表进行大量DML操作,应考虑将位图索引删除,DML操作结束后重建位图索引;

    不适用于频繁持续发生DML操作的OLTP系统,会出现行锁定,阻碍更新性能;

    1.2 使用位图索引

    位图索引与B-TREE索引有很大的不同,一个位图索引由多个位串组成,每个位串都表示基础列中一个独立的有效值;每个位串是打开或关闭,表示该值是否用于某一行;人员信息表th03 为例,性别(gender)字段的值(男、女、未记录),假如为其创建位图索引,那么每个位串(男、女、未记录)中的单个位表示一个给定行的值是男、女还是未记录;当判断某一列是否适合创建位图索引时,需要考虑是否符合”低基数列”,根据应用程序、数据组成以及数据库中的表的情况不同,是否创建位图索引的结论也可能不同;通常用来判断的一条基本经验法则是:”如果该列的有效值数目不足表中行数的1%,那么它就适合创建位图索引,th03来说,表行数为:500万行,而性别列的有效值数目仅为3( 男、女、未记录),可以确定它适合创建位图索引:

    人员信息表(th03)

    ID

    NAME

    GENDER

    IDCARD

    HOMEADDR

    JOBNO

    BIRTHDATE

    1

    998698

    李天

    440623197007253619

    水晶洞1

    526456

    25-JUL-70

    2

    998699

    李花

    510802197007251223

    水晶洞2

    5785452

    25-JUL-70

    3

    584625

    李某

    未记录

    564551545265642155

    水晶洞3

    1565452

    01-JUL-88

    SQL> create bitmap index ind_th03_gender on th03(gender) tablespace tbs03;

    Index created.

    Elapsed: 00:00:01.05

    SQL> execute dbms_stats.gather_table_stats('sywu','th03',cascade=>true);

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:04.31

    SQL> @/oracle/getind

    TABLE_NAME  INDEX_NAME  COLUMN_NAME   SIZE_GB  INDEX_TY  STATUS  NUM_ROWS  DISTINCT_KEYS

    ------------------------------ ------------------------------ ------------------------------ ---------- -------- -------- --------- --------

    TH03                 IND_TH03_GENDER   GENDER   .001953125   BITMAP    VALID     453    3

    位图索引的创建非常快并且占用的空间也非常小;位图索引和B-TREE索引存储值的方式不同,它存储表中的每一行值(包括空值),对于B-TREE索引,单列索引不存储空值,复合索引只要有一个非空值就可以存储;所以当执行 IS NULL 或者 IS NOT NULL 查询时位图索引的效率要高于B-TREE索引:

    SQL> select count(*) from th03 where gender is null;

    Elapsed: 00:00:00.03

    -----------------------------------------------------------------------------------------------

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

    -----------------------------------------------------------------------------------------------

    | 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |

    | 1 | SORT AGGREGATE | | 1 | 5 | | |

    | 2 | BITMAP CONVERSION COUNT | | 1 | 5 | 1 (0)| 00:00:01 |

    |* 3 | BITMAP INDEX SINGLE VALUE| IND_TH03_GENDER | | | | |

    -----------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

    3 - access("GENDER" IS NULL)

    Statistics

    ----------------------------------------------------------

    1 recursive calls

    0 db block gets

    2 consistent gets

    1 physical reads

    如果在相同表相同列建立B-TREE索引,则该执行必须全表扫描:

    SQL> select count(*) from th03 where gender is null;

    Elapsed: 00:00:00.16

    ---------------------------------------------------------------------------

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

    ---------------------------------------------------------------------------

    | 0 | SELECT STATEMENT | | 1 | 5 | 14908 (1)| 00:02:59 |

    | 1 | SORT AGGREGATE | | 1 | 5 | | |

    |* 2 | TABLE ACCESS FULL| TH03 | 1 | 5 | 14908 (1)| 00:02:59 |

    ---------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

    2 - filter("GENDER" IS NULL)

    Statistics

    ----------------------------------------------------------

    1 recursive calls

    0 db block gets

    54574 consistent gets

    54562 physical reads

    位图索引还可以在另外一些情况使用,如使用聚合函数:

    SQL> select count(*),count(gender) from th03;

    COUNT(*) COUNT(GENDER)

    ---------- -------------

    5000000 5000000

    Elapsed: 00:00:00.11

    -------------------------------------------------------------------------------------------------

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

    -------------------------------------------------------------------------------------------------

    | 0 | SELECT STATEMENT | | 1 | 5 | 203 (0)| 00:00:03 |

    | 1 | SORT AGGREGATE | | 1 | 5 | | |

    | 2 | BITMAP CONVERSION TO ROWIDS | | 5000K| 23M| 203 (0)| 00:00:03 |

    | 3 | BITMAP INDEX FAST FULL SCAN| IND_TH03_GENDER | | | | |

    -------------------------------------------------------------------------------------------------

    Statistics

    ----------------------------------------------------------

    0 recursive calls

    0 db block gets

    232 consistent gets

    0 physical reads

    如果你决定在应用程序中实现位图索引,应时不时的检查建立了位图索引的列的数据组成,这一点很重要,如果你算错了包含位图索引的任何列的基数,可能会对应用程序造成负面影响,(位图索引的存储空间会增加、查询性能会降低、重建索引的时间将增加);

    1.3 位图索引对DML操作的影响:

    为了便于测试,我创建另一张分区表 th04,并为th04的性别、出生日期列创建位图索引,最后向它插入100万行数据:

    SQL> create table th04

    partition by range(id)(

    partition th04_part1 values less than(1000000) tablespace tbs03,

    partition th04_part2 values less than(2000000) tablespace tbs03,

    partition th04_part3 values less than(3000000) tablespace tbs03,

    partition th04_part4 values less than(4000000) tablespace tbs03,

    partition th04_part5 values less than(5000000) tablespace tbs03,

    partition th04_part6 values less than(maxvalue) tablespace tbs03

    ) as select * from tbbase where 1=0 ;

    Table created.

    Elapsed: 00:00:00.04

    SQL> create bitmap index ind_th04_gender on th04(gender) local tablespace tbs03;

    Index created.

    Elapsed: 00:00:00.04

    SQL> create bitmap index ind_th04_birthdate on th04(birthdate) local tablespace tbs03;

    Index created.

    Elapsed: 00:00:00.09

    SQL>insert into th04 select * from tbbase where rownum<1000001;

    1000000 rows created.

    Elapsed: 00:00:40.24

    从结果可以看出,插入100万行的数据花费40,从表面上看花费的时间似乎是合理的,但当数据量不断增加时,特别对于一个数据仓库环境,一天处理几百万甚至亿行数据是司空见惯的,合理的情况应考虑删除位图索引(如果是分区表则将目标分区标记为不可用),执行完DML加载操作后重建位图索引;

    SQL> alter index IND_TH04_GENDER unusable;

    Index altered.

    Elapsed: 00:00:00.18

    SQL> alter index IND_TH04_BIRTHDATE unusable;

    Index altered.

    Elapsed: 00:00:00.41

    SQL> insert into th04 select * from tbbase where rownum<1000001;

    1000000 rows created.

    Elapsed: 00:00:23.74

    禁用位图索引后,插入100万行数据只花费23 ,对于大数据而言这或许可以提高装载数据的性能;数据装载结束后重建位图索引:

    SQL> alter index ind_th04_gender rebuild partition TH04_PART1;

    alter index ind_th04_gender rebuild partition TH04_PART2;

    alter index ind_th04_gender rebuild partition TH04_PART3;

    alter index ind_th04_gender rebuild partition TH04_PART4;

    alter index ind_th04_gender rebuild partition TH04_PART5;

    alter index ind_th04_gender rebuild partition TH04_PART6;

    alter index ind_th04_birthdate rebuild partition th04_part1;

    .........

    重建分区索引也可以通过下面的命令重建:

    SQL> alter table th04 modify partition th04_part1 rebuild unusable local indexes;

    Table altered.

    Elapsed: 00:00:05.81

    此命令虽然简单,但它也有不足之处,对于一个指定的分区,它只能按顺序执行;而对于每一个指令发出的命令重建分区,它可以同时执行多个语句,实现并行重建索引;

    除装载数据的影响外,位图索引也会影响数据的DML操作,请观察下面的人员信息表数据:

    人员信息表(th04)

    ID

    NAME

    GENDER

    IDCARD

    HOMEADDR

    JOBNO

    BIRTHDATE

    1

    998698

    李天

    440623197007253619

    水晶洞1

    526456

    25-JUL-70

    2

    998699

    李四

    510802197007251223

    水晶洞2

    5785452

    25-JUL-70

    .....

    ....







    数据显示他们的出生日期是相同的,并且出生日期列(BIRTHDATE)还建立了位图索引( IND_TH04_BIRTHDATE) ,因业务错误记录两人的出生日期,so,现在对其修改:

    ---session 1-----

    SQL> select distinct sid from v$mystat;

    SID

    ----------

    191

    SQL> update th04 set birthdate='26-JUL-70' where idcard='440623197007253619';

    1 row updated.

    Elapsed: 00:00:00.11

    此时session 1 的用户因为业务繁忙没有及时提交,这时另一个业务员在新的会话 session2中修该另一个错误记录:

    ----session 2 -----

    SQL> select distinct sid from v$mystat;

    SID

    ----------

    194

    SQL> update th04 set birthdate='27-JUL-70' where idcard='510802197007251223';

    session 2 中的用户会一直处于等待状态,因为他们修改的错误人员出生日期在更新之前在同一个位图索引位串中,当修改位串中的位信息时位串会被锁定,直到更新提交后更新位串中的位值;观察此时的锁状态:

    SQL>select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in(191,194) order by sid

    SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

    ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

    191 AE 100 0 4 0 2052 0

    191 TM 75667 0 3 0 263 0

    191 TX 65562 848 6 0 263 1

    191 TM 75668 0 3 0 263 0

    191 TO 65927 1 3 0 286 0

    194 TM 75668 0 3 0 238 0

    194 TX 131081 1071 6 0 238 0

    194 TM 75667 0 3 0 238 0

    194 TX 65562 848 0 4 238 0

    194 AE 100 0 4 0 1406 0

    对于session 1(191)此时持有一个6级事务锁,并且堵塞session 2(194),它们请求的资源是一样的,这并非巧合;只有当session 1(191)提交或回退后,这个6级事物锁才会被释放,session 2(194)才能持有锁修改数据;

    SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

    ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

    191 AE 100 0 4 0 2832 0

    191 TO 65927 1 3 0 1066 0

    194 TX 131081 1071 6 0 1018 0

    194 TM 75667 0 3 0 1018 0

    194 TM 75668 0 3 0 1018 0

    194 AE 100 0 4 0 2186 0

    所以建立位图索引时,应仔细分析表结构和表数据,作出明智、合理选择;以上测试因环境、版本、数据库状态测试结果可能不同;


    2.位图连接索引

    2.1 明确需求后使用位图索引

    在创建位图连接索引时,它是两个表或多个表之间的索引值的连接,连接的结果存储在索引自身中;通过前期做连接并存储结果,当查询时通过扫描索引(避免两表或多表全表扫描)来获取数据,当然对于建立这样的索引,建立前需求必须明确; 请观察如下人员信息表(th04)和人员单位关系信息表(tbbsj)

    人员信息表(th04)

    ID

    NAME

    GENDER

    IDCARD

    HOMEADDR

    JOBNO

    BIRTHDATE

    1

    789524

    张三

    429005198911261805

    水晶洞1

    1300440

    05-AUG-79

    2

    564895

    李四

    429005198911296178

    水晶洞2

    1000209

    14-AUG-79

    .....

    ....








    人员单位关系信息表(tbbsj)

    ID(单位ID)

    IDCARD(人员身份证)

    DNAME(单位名称)

    JOINDATE(进入单位时间)

    SRZW(所任职位)

    GZZT(工作状态)

    TSGX(特殊贡献)

    1

    785652

    429005198911261805

    太空建筑有限公司

    2013-12-22 21:51:33

    总经理助理

    暂无

    2

    5689556

    429005198911296178

    飞轮实业有限公司

    2013-12-22 21:51:42

    总经理小秘

    暂无

    ..

    .....








    已知人员信息数据量为:100,人员单位关系信息数据量为:1106642,在建立普通索引的情况下获取人员IDCARD:429005198911261805 的人员单位信息:

    SQL> select t1.*,t2.* from th04 t1,tbbsj t2

    2 where t1.idcard=t2.idcard and t1.idcard='429005198911261805';

    ----------------------------------------------------------------------------------------------

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

    ----------------------------------------------------------------------------------------------

    | 0 | SELECT STATEMENT | | 1 | 253 | 10959 (1)| 00:02:12 | | |

    |* 1 | HASH JOIN | | 1 | 253 | 10959 (1)| 00:02:12 | | |

    | 2 | PARTITION RANGE ALL| | 1 | 65 | 2661 (1)| 00:00:32 | 1 | 6 |

    |* 3 | TABLE ACCESS FULL | TH04 | 1 | 65 | 2661 (1)| 00:00:32 | 1 | 6 |

    |* 4 | TABLE ACCESS FULL | TBBSJ | 1 | 188 | 8297 (1)| 00:01:40 | | |

    ----------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

    1 - access("T1"."IDCARD"="T2"."IDCARD")

    3 - filter("T1"."IDCARD"='429005198911261805')

    4 - filter("T2"."IDCARD"='429005198911261805')

    Statistics

    ----------------------------------------------------------

    1 recursive calls

    0 db block gets

    40165 consistent gets

    40137 physical reads

    分析结果得知为获取IDCARD: 429005198911261805的人员单位信息,两张大表都做了全表扫描;因为获得人员单位关系信息需求已经明确,为其创建位图连接索引:

    SQL> create bitmap index ind_th04uniontbbsj on th04(t1.idcard)

    2 from th04 t1,tbbsj t2

    3 where t1.idcard=t2.idcard

    4 tablespace tbs03

    5* local

    SQL> /

    Index created.

    Elapsed: 00:00:01.65

    创建位图连接索引后再次查询:

    SQL> select t1.*,t2.* from th04 t1,tbbsj t2

    2* where t1.idcard=t2.idcard and t1.idcard='429005198911261805';

    -----------------------------------------------------------------------------------------------------------------

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

    -----------------------------------------------------------------------------------------------------------------

    | 0 | SELECT STATEMENT | | 1 | 253 | 5 (0)| 00:00:01 | | |

    | 1 | NESTED LOOPS | | 1 | 253 | 5 (0)| 00:00:01 | | |

    | 2 | TABLE ACCESS BY INDEX ROWID | TBBSJ | 1 | 188 | 3 (0)| 00:00:01 | | |

    |* 3 | INDEX UNIQUE SCAN | PK_IDCARD | 1 | | 2 (0)| 00:00:01 | | |

    | 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| TH04 | 1 | 65 | 2 (0)| 00:00:01 | ROWID | ROWID |

    |* 5 | INDEX UNIQUE SCAN | CS_IDCARD | 1 | | 1 (0)| 00:00:01 | | |

    -----------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

    3 - access("T2"."IDCARD"='429005198911261805')

    5 - access("T1"."IDCARD"='429005198911261805')

    Statistics

    ----------------------------------------------------------

    0 recursive calls

    0 db block gets

    3 consistent gets

    0 physical reads

    对于位图连接索引,它将主导表和关联表中的ROWID以及主导列的列值存储在索引中,查询数据时通过扫描索引提高查询效率:

    人员信息表(TH04)ROWID

    人员单位关系信息表(tbbsj)ROWID

    IDCARD

    AAASdEAAGAAB6w6AAY

    AAASfXAAIAAAmCQAAE

    440621197102274116

    AAASdEAAGAAB6z5AAf

    AAASfXAAIAAAmCGAAX

    440621197311244423

    AAASdEAAGAAB6zCAAl

    AAASfXAAIAAAmCPAAW

    440621197405202427


    2.2创建位图连接索引的注意事项:

    2.2.1创建位图连接索引时WHERE 子句中的关联条件列必须是主键唯一约束(不符合条件会报错ORA-25954: missing primary key or unique constraint on dimension);

    2.2.2在指定索引列时,如果两表都具有相同列,必须使用表名.列名的方式指定或者别名.列名的方式指定(不符合条件会报错:ORA-00918: column ambiguously defined);

    2.2.3 创建位图索引同样DML操作效率差,建立前请仔细分析表结构和数据DML操作率;

    2.2.4 更新表数据时同样会有锁定主导表、关联表问题;




    转载于:https://www.cnblogs.com/lanston/p/3495864.html

    展开全文
  • Oracle关于位图索引创建与应用

    千次阅读 2018-03-18 13:05:01
    Oracle关于位图索引创建与应用 标签: Oracle 位图索引 2016年03月13日 10:55:383730人阅读 评论(0) 收藏 举报  分类: 数据库(10) oracle(12)  1)创建  CREATE ...
  • Oracle位图索引

    千次阅读 2014-07-24 08:52:31
    位图索引主要针对大量相同值的列而创建(例如:类别,操作员,部门ID,库房ID等), 索引块的一个索引行中存储键值和起止Rowid,以及这些键值的位置编码, 位置编码中的每一位表示键值对应的数据行的有无.一个位图索引...
  • 1.位图索引 位图索引适用于性别、婚姻状态、行政区等只有几列固定值的类型列,身份证号等就不适合位图索引位图索引适用于静态数据,频繁更新的字段不适用... 位图索引创建 create bitmap index index_name o...
  • 位图索引Oracle为每个唯一键创建一个位图,然后把与键值所关联的ROWID保存为位图。最多可以包括30列。 一般情况下,大多数用户都只创建TYPE为NORMAL的B-树索引,所以对于较低基数的列我们都是不创建索引的,因为B...
  • oracle中的位图索引

    2018-03-15 10:41:55
    原理 使用须知 如果用户查询的列的基数非常的小, 即只有的几个固定值,如性别、婚姻状况、行政区等等。要为这些基数值比较小的...位图索引创建时,不需要进行排序,因此速度较快;而B-tree索引创建时,需要排...
  • oracle位图索引

    千次阅读 2010-09-26 11:33:00
    在实际应用中,如果某个字段的值需要频繁更新,那么就不适合在它上面创建位图索引。在位图索引中,如果你更新或插入其中一条数值为N的记录,那么相应表中数值为N的记录(可能成百上千条)全部被Oracle锁定,这就意味...
  • Oracle B树索引和位图索引简介

    千次阅读 2019-01-23 17:43:47
    创建索引时它就是默认的索引类型。B 树索引可以是一个列的(简单)索引,也可以是多个列的(组合/复合)索引。B 树索引最多可以包括32 列。 在下图的例子中,B 树索引位于雇员表的 last_name 列上。这个索引的二元...
  • 3、创建需要的时间不同:位图索引创建很快,而B-tree索引创建很耗时。 4、dml操作影响大小不同:位图索引只适合海量查询,update、insert、delete时会产生负影响,而B-tree索引适合频繁的dm...
  • Oracle位图索引引发的阻塞与死锁

    千次阅读 2016-09-18 16:27:15
    前面我介绍了itl引发的阻塞与死锁,这里有必要再介绍一下位图索引引发的阻塞与死锁,因为这个也是不同于...假定,一个表,上面有标志字段(flags),分别是(0、1),而我们在这个flag字段上创建了一个位图索引,那么,现
  • 概述oracle索引主要分为以下几种:1. b-tree索引Oracle数据库中最常见的索引...2. 位图索引(bitmap index)位图索引特定于该列只有几个枚举值的情况,比如性别字段,标示字段比如只有0和1的情况。3. 基于函数的索引...
  • 转载自:http://blog.itpub.net/519536/viewspace-611296/通过以下实验,来验证Bitmap位图索引较之普通的B-Tree索引锁的“高昂代价”。...1)在表t_bitmap上创建位图索引SEC@ora11g> create table t_
  • 通过以下实验,来验证Bitmap位图索引较之普通的B-Tree索引锁的“高昂代价”。...1)在表t_bitmap上创建位图索引 1 2 3 4 ora11g> create table t_bitmap
  • oracle 创建索引

    千次阅读 多人点赞 2014-12-31 09:19:51
    oracle的索引分为5种:唯一索引,组合索引,反向键索引,位图索引,基于函数的索引 创建Oracle索引的标准语法: CREATE INDEX 索引名 ON 表名 (列名) TABLESPACE 表空间名; 创建唯一索引: CREATE unique ...
  • 在实际应用中,如果某个字段的值需要频繁更新,那么就不适合在它上面创建位图索引。在位图索引中,如果你更新或插入其中一条数值为n的记录,那么相应表中数值为n的记录(可能成百上千条)全部被oracle锁定,这就...
  • 笔者借这个机会,跟大家讨论一下位图索引的使用时机以及注意事项。  一、 B树索引的缺陷。  在实际工作中,B树索引是Oracle数据库中最常用的一种索引。如在使用Create Index语句创建索引的时候,默认采用的就

空空如也

空空如也

1 2 3 4 5 ... 14
收藏数 264
精华内容 105
关键字:

创建位图索引oracle