图片 1

Oracle索引总结

位图索引

1.1 位图索引概述

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

 

1.2 位图索引结构的说明

图片 1

 

与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                   8SCOTT                 10SYS                 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 statementStatistics----------------------------------------------------------     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 statementStatistics----------------------------------------------------------     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 statementStatistics----------------------------------------------------------     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 statementStatistics----------------------------------------------------------     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 TABLESYS                  44       44 INDEXSYS                  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数据库而言,位图索引不推荐使用。

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注