阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

Oracle索引种类之位图索引

178次阅读
没有评论

共计 8132 个字符,预计需要花费 21 分钟才能阅读完成。

位图索引

1.1 位图索引概述

  • 位图索引通过位图向量,表示索引键值在表中的分布。
  • 适用于没有大量更新操作的对象,如:OLAP 数据库。
  • 对于存在大量更新操作的索引列,不适用位图索引。因此对于 OLTP 并不适用。
    • 更新位图向量时,相应位图涉及的所有数据行会被锁定,无法针对这些数据行的该索引列进行 DML 操作

1.2 位图索引结构的说明

Oracle 索引种类之位图索引

与 B -tree 索引的联系及区别如下:

  • 与 B -tree 索引的联系:位图索引使用 B -tree 形式组成。
  • 与 B -tree 索引的区别:位图索引的一个索引键值对应一个叶子节点。(B-tree 的叶节点包含多个索引键值)
  • 与 B -tree 索引的区别:位图索引使用位图向量标识键值对应的数据行分布情况。(B-tree 采用 rowid 定位数据行)

此外:

  • 位图索引创建时,不需要进行排序,因此速度较快;而 B -tree 索引创建时,需要排序等操作,因此慢很多。
  • 位图索引允许键值为 NULL,因此进行 NULL 条件查询时,可以使用索引。而 B -tree 索引不记录 NULL(组合索引除外),因此会使用全表扫描。
  • 对于表的访问效率很高
    • 当使用 count(XX), 可以直接访问索引就快速得出统计数据.
    • 当根据位图索引的列进行 and,or 或 in(x,y,..) 查询时, 直接用索引的位图进行或运算, 在访问数据之前可事先过滤数据 .

需要重申的是:

  • B 树索引中,由于根据键值的 rowid 定位数据行,因此相同的键值存在多次,与相应的 rowid 对应。
  • 位图索引中,由于根据位图向量定位键值所在的数据行,因此相同键值只需要记录一次。

1.3 位图索引对于执行计划的影响

对于 B -tree 索引,CBO 优化器会根据数据的具体分布情况,选择是否应用索引。

对于位图索引,即使读取非常大的数据,仍然会选择索引。

-- 查看测试数据表的结构 
Yumiko@sunny >desc test01; Name Null? Type --------------------------------------------- -------- ---------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1)

-- 查看表的总行数 Yumiko
@sunny >select count(*) from test01; COUNT(*) ---------- 22928


-- 查看 owner 字段的数据分布情况,可以发现 owner 字段的含 SYS 的数据非常庞大,存在明显的数据倾斜
-- 准备为 owner 字段分两次,建立 b -tree 索引以及位图索引,通过相同的查询条件,比较索引的使用情况
Yumiko
@sunny >select owner,count(*) from test01 group by owner order by count(*); OWNER COUNT(*) ------------------------------ ---------- BI 8 SCOTT 10 SYS 22910

-- 建立 b -tree 索引并验证 Yumiko
@sunny >create index BTREE_OWNER on test01(owner); Index created.
Yumiko
@sunny >select INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes where TABLE_NAME = 'TEST01'; INDEX_NAME INDEX_TYPE TABLE_NAME ------------------------------ --------------------------- ------------------------------ BTREE_OWNER NORMAL TEST01
-- 打开会话根据,已查看执行计划 Yumiko
@sunny >set autotrace trace




-- 查询 owner 字段为 BI 时,由于从较多数据中检查个别数据,执行计划用到了索引扫描,通过索引块,快速定位目标数据的 rowid,进而访问数据块获得结果。此时效率最高
Yumiko
@sunny >select * from test01 where owner='BI'; 8 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 725909888 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 1416 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST01 | 8 | 1416 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | BTREE_OWNER | 8 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER"='BI') Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 40 recursive calls 0 db block gets 84 consistent gets 4 physical reads 0 redo size 2010 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 8 rows processed

-- 查询 owner 字段为 SYS 时,由于数据中该字段的数据存在明显的数据倾斜,若通过索引方式检索,将先扫面大部分的索引块,然后再根据 rowid 查找数据块,此时代价十分大,不如直接全表扫描效率高,因此不用索引。
Yumiko
@sunny >select * from test01 where owner='SYS'; 22910 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 262542483 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 22064 | 3813K| 159 (2)| 00:00:02 | |* 1 | TABLE ACCESS FULL| TEST01 | 22064 | 3813K| 159 (2)| 00:00:02 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OWNER"='SYS') Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 7 recursive calls 0 db block gets 2288 consistent gets 8 physical reads 0 redo size 1148463 bytes sent via SQL*Net to client 17266 bytes received via SQL*Net from client 1529 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 22910 rows processed

-- 删除 B -tree 索引,创建位图索引并查看 Yumiko
@sunny >create bitmap index bitmap_owner on test01(owner); Index created. Yumiko@sunny >select INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes where TABLE_NAME = 'TEST01'; INDEX_NAME INDEX_TYPE TABLE_NAME ------------------------------ --------------------------- ------------------------------ BITMAP_OWNER BITMAP TEST01


-- 打开会话跟踪,以查看执行计划 Yumiko
@sunny >set autotrace trace


-- 当查看 owner 为 BI 时,与 b -tree 一样,使用索引扫描的方式进行检索。 Yumiko
@sunny >select * from test01 where owner='BI'; 8 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3098739824 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 1416 | 55 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | TEST01 | 8 | 1416 | 55 (0)| 00:00:01 | | 2 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 3 | BITMAP INDEX SINGLE VALUE | BITMAP_OWNER | | | | | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("OWNER"='BI') Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 40 recursive calls 0 db block gets 82 consistent gets 0 physical reads 0 redo size 2010 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 8 rows processed


-- 当查看 owner 为 SYS 时,此时与 B 树索引不同,依然选择了位图索引
Yumiko
@sunny >select * from test01 where owner='SYS'; 22910 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3098739824 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 22064 | 3813K| 55 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | TEST01 | 22064 | 3813K| 55 (0)| 00:00:01 | | 2 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 3 | BITMAP INDEX SINGLE VALUE | BITMAP_OWNER | | | | | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("OWNER"='SYS') Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 7 recursive calls 0 db block gets 2130 consistent gets 0 physical reads 0 redo size 2526059 bytes sent via SQL*Net to client 17266 bytes received via SQL*Net from client 1529 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 22910 rows processed

从上面可以看到,对于位图索引,即使从表中读取很多行,也会使用索引。

1.4 DML 操作对于位图索引的影响

对于位图索引,当一个事务更新一条记录的索引列键值,且未提交事务时,其他事务对于该索引键值涉及的数据行的该索引列值的修改,将处于等待。

-- 查看当前两个会话的事件,无特殊事件 
Yumiko@sunny >select SID,USERNAME,PROGRAM,EVENT from v$session where username='SCOTT'; SID USERNAME PROGRAM EVENT ------- ----------------- ------------------------------------------------------------- 147 SCOTT sqlplus@OA01 (TNS V1-V3) SQL*Net message from client 153 SCOTT sqlplus@OA01 (TNS V1-V3) SQL*Net message from client

-- 查看表的前三行数据 Yumiko
@sunny01 >select OWNER,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from test01 where rownum < 4; OWNER OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE ------------------------------ ---------- -------------- ------------------- SYS 20 2 TABLE SYS 44 44 INDEX SYS 28 28 TABLE
-- 发起事务 1 对于位图索引列 owner 为 SYS 值的一行数据的 owner 字段的更新操作,但不提交事务。 Yumiko
@sunny01 >update test01 set OWNER='SCOTT' where DATA_OBJECT_ID=28; 1 row updated.

-- 发起事务 2 对于位图索引列 owner 为 SYS 值的另一行数据库的 owner 字段的更新操作,此时事务停顿。 Yumiko@sunny02
>update test01 set OWNER='BI' where DATA_OBJECT_ID=44;

-- 查看此时会话的事件,发现有 row lock 等待事件 Yumiko
@sunny >select SID,USERNAME,PROGRAM,EVENT from v$session where username='SCOTT'; SID USERNAME PROGRAM EVENT --------- ------------------------------ ------------------------------------------------ 147 SCOTT sqlplus@OA01 (TNS V1-V3) enq: TX - row lock contention 153 SCOTT sqlplus@OA01 (TNS V1-V3) SQL*Net message from client

通过上面的示例证明了对于位图索引列的更新,相应键值的位图向量涉及的数据行在键值更新事务提交前,会始终处于锁定状态,其他事务无法对这些数据行的该索引列进行 DML 操作。

因此,对于 DML 操作频繁度较高的 OLTP 数据库而言,位图索引不推荐使用。

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 :http://www.linuxidc.com/Linux/2016-10/135702.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计8132字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中