sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA > Oracle性能优化 >

【学习笔记】Oracle位图索引与B树索引区别与使用方法案例

时间:2016-12-22 22:22   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库位图索引与B树索引区别与使用方法区别。

位图索引与B*树索引不同,在位图索引中,Oracle为每一列的唯一值创建一个位图。每个位图为表中的每一个记录包含一个位(0或1),1表示包含该值,0表示不包含。

下面,我们看来一下位图索引的结构。
假如表employees的表结构与数据如下:
ename             gender  salary        dept_id
SNISSFQGFF   M     3121.26          7                                            
ZDXMVQUAZA  F     4177.26         34 
BYQKBWILEF  M    13649.17         11                                             
..................................................................
我们对表employees的ename,gender,dept_id创建位图索引,结构如下:

ename列中的数据:
SNISSFQGFF ZDXMVQUAZA BYQKBWILEF 
1                           0                       0
0                           1                       0
0                            0                       1
gender列中的数据:
M            F
1             0
0             1
1             0
dept_id列中的数据:
7       34         11
1         0           0
0         1           0
0          0          1
假如有这样的SQL语句进行查询:
select * from employees where ename = 'SNISSFQGFF' and gender = 'M' and dept_id = '7';

那么对于位图索引,就是把上面的结果执行与(and)运算。(如果SQL语句中的谓词使用的是or,那么,对于位图索引使用的是或运算)。
SNISSFQGFF                            M                    7
1                             and              1        and       1          =             1
0                                                  0                     0                         0
0                                                  1                     0                         0
这样,记录已经找到。

从上述这个结构,我们应该已经知道位图使用的场合了,就是对于相异基数较低的情况下适合。比如一个列的值只有 Y,N,NULL,那么,可以考虑使用位图索引。位图索引较多用于数据仓库/即席查询当中,在OLTP中,不太适合使用。如果系统中的数据会由多个会话并发更新时,也不适用于位图索引。位图索引有以下几个特性:

对于相异基数较低的列,位图索引比较B*树索引有效。对于一个列如果接近唯一或是唯一值,B*树索引通常会比位图索引有效;
位图索引的合并,比B*树索引的合并要高效;
位图索引特别适合大表查询的聚集,比如,“多少”,“总和”之类的;
对于位图连接索引,可用来优化表的连接查询。


然而,位图索引也存在一些缺点,如下:
对于创建位图索引的列,如果对其中的一个值进行更新,那么与这个值相同的所有行都将被锁。这也是不太适合OLTP的原因。位图索引不能用来优化范围查询。

下面, 我以例子的形式进行说明,并与B*树索引进行一个对比。
SQL> create table t
  2  as
  3  select rownum emp_id,dbms_random.string('u',15) ename,
  4         decode(round(dbms_random.value(1,2)),1,'M',2,'F') gender,
  5         trunc(dbms_random.value(1,50)) loc_num,
  6         decode(round(dbms_random.value(1,5)),1,'18 and under',2,'19-25',
  7               3,'26-30',4,'31-40',5,'41 and over') age_group
  8  from dual
  9  connect by rownum <= 100000
10  /

Table created.

SQL> alter table t add constraint t_pk primary key(emp_id)
Table altered.

SQL> create bitmap index gender_idx on t(gender)
Index created.

SQL> create bitmap index loc_num_idx on t(loc_num)
Index created.

SQL> create bitmap index age_group_idx on t(age_group)
Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true)

PL/SQL procedure successfully completed.


说明:上面,我使用随机数生成一张表,并对gender,loc_num,age_grouip列创建了位图索引。
下面,我们来看几个查询,并查看其执行计划。
********************************************************************************

select count(*)
from t
where gender = 'M'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          4          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 33 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=4 pr=0 pw=0 time=0 us)
      5   BITMAP CONVERSION COUNT (cr=4 pr=0 pw=0 time=84 us cost=3 size=100000 card=50000)
      5    BITMAP INDEX SINGLE VALUE GENDER_IDX (cr=4 pr=0 pw=0 time=44 us)(object id 15218)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00


********************************************************************************
说明:对于这样的查询,创建位图索引,是非常高效的。如果对其进行创建B*树索引,不会走索引,它会进行全表扫描。

********************************************************************************

select count(*)
from t
where gender = 'M'
  and loc_num in (1,36,43)
  and age_group = '19-25'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         17          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         17          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 33 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=17 pr=0 pw=0 time=0 us)
      1   BITMAP CONVERSION COUNT (cr=17 pr=0 pw=0 time=0 us cost=9 size=7956 card=612)
      1    BITMAP AND  (cr=17 pr=0 pw=0 time=0 us)
      1     BITMAP OR  (cr=9 pr=0 pw=0 time=0 us)
      1      BITMAP INDEX SINGLE VALUE LOC_NUM_IDX (cr=2 pr=0 pw=0 time=0 us)(object id 15219)
      1      BITMAP INDEX SINGLE VALUE LOC_NUM_IDX (cr=3 pr=0 pw=0 time=0 us)(object id 15219)
      1      BITMAP INDEX SINGLE VALUE LOC_NUM_IDX (cr=4 pr=0 pw=0 time=0 us)(object id 15219)
      5     BITMAP INDEX SINGLE VALUE AGE_GROUP_IDX (cr=4 pr=0 pw=0 time=12 us)(object id 15220)
      5     BITMAP INDEX SINGLE VALUE GENDER_IDX (cr=4 pr=0 pw=0 time=16 us)(object id 15218)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************
说明:对于上述查询:首先是使用索引LOC_NUM_IDX这三个索引进行了或(OR)运算,接着是索引AGE_GROUP_IDX,GENDER_IDX的访问,然后进行索引LOC_NUM_IDX或运算的结果和AGE_GROUP_IDX,GENDER_IDX扫描的结果进行与(and)运算。最后进行了count运算。从这个查询我们发现,位图索引得到了有效的合并。在创建的时候,我没有创建组合索引,而是对每一列创建索引,优化器对位图进行了合理有效的组合。

********************************************************************************

select ename
from t
where ( (gender = 'M' and loc_num = 1)
     or (gender = 'F' and loc_num = 2)
      )
   and age_group = '18 and under'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       19      0.00       0.00          0        230          0         265
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       21      0.00       0.00          0        230          0         265

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 33 

Rows     Row Source Operation
-------  ---------------------------------------------------
    265  TABLE ACCESS BY INDEX ROWID T (cr=230 pr=0 pw=0 time=1980 us cost=70 size=11832 card=408)
    265   BITMAP CONVERSION TO ROWIDS (cr=16 pr=0 pw=0 time=440 us)
      1    BITMAP AND  (cr=16 pr=0 pw=0 time=0 us)
      4     BITMAP INDEX SINGLE VALUE AGE_GROUP_IDX (cr=3 pr=0 pw=0 time=12 us)(object id 15220)
      1     BITMAP OR  (cr=13 pr=0 pw=0 time=0 us)
      1      BITMAP AND  (cr=7 pr=0 pw=0 time=0 us)
      1       BITMAP INDEX SINGLE VALUE LOC_NUM_IDX (cr=3 pr=0 pw=0 time=0 us)(object id 15219)
      5       BITMAP INDEX SINGLE VALUE GENDER_IDX (cr=4 pr=0 pw=0 time=16 us)(object id 15218)
      1      BITMAP AND  (cr=6 pr=0 pw=0 time=0 us)
      1       BITMAP INDEX SINGLE VALUE LOC_NUM_IDX (cr=2 pr=0 pw=0 time=0 us)(object id 15219)
      5       BITMAP INDEX SINGLE VALUE GENDER_IDX (cr=4 pr=0 pw=0 time=16 us)(object id 15218)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      19        0.00          0.00
  SQL*Net message from client                    18        0.02          0.14
********************************************************************************
说明:该查询进行了索引之间的合并,与上述类似。这里由于要访问表中具体的行,所以位图索引需要把位转换成rowid,并进行对表的访问。

下面我们来看一下对位图索引键进行更新的情况。
Session A:
SQL> select emp_id
  2  from t
  3  where loc_num = 22
  4    and rownum <= 5
  5  /

    EMP_ID
----------
        58
        60
        75
        83
       116

SQL> update t set loc_num = 60
  2  where loc_num = 22
  3    and emp_id = 60
  4  /

1 row updated.

SQL>
(没有提交或回滚)
Session B:
SQL> update t set loc_num = 80
  2  where loc_num = 22
  3    and emp_id = 83
  4  /
(处于挂起状态)

说明:对于上述情况,Session A和Session B更新的是两行不同的数据,而却出列了阻塞现象。这是因为在loc_num列上创建了位图索引,当位图索引的一个位上的对应的一行数据发生更新时,连同该位所对应的其他行也将被锁。把上述的更新换成删除,也会出现该现象。

对于位图索引,我们也可以创建位图连接索引。位图连接索引允许使用一个表的列对别一个表建立索引。例子如下:
SQL> create table emp
  2  as
  3  select rownum empno,dbms_random.string(1,10) ename,
  4         trunc(dbms_random.value(1,6)) deptno
  5  from dual
  6  connect by rownum <= 30
  7  /

Table created.

SQL> create table dept
  2  as
  3  select rownum deptno,dbms_random.string(1,6) dname
  4  from dual
  5  connect by rownum <= 6
  6  /

Table created.

SQL> alter table emp add constraint emp_pk primary key(empno)
Table altered.

SQL> alter table emp modify ename varchar2(10)
Table altered.

SQL> alter table dept add constraint dept_pk primary key(deptno)
Table altered.

SQL> alter table dept modify dname varchar2(6)
Table altered.

SQL> alter table emp add constraint deptno_fk foreign key(deptno) references dept(deptno)
Table altered.


下面,我创建一个位图连接索引
SQL> create bitmap index emp_dname_idx on emp(d.dname)
  2  from emp e,dept d
  3  where e.deptno = d.deptno
  4  /

Index created.

SQL> begin
  2     dbms_stats.gather_table_stats(user,'emp',cascade=>true);
  3     dbms_stats.gather_table_stats(user,'dept',cascade=>true);
  4  end;
  5  /

PL/SQL procedure successfully completed.

位图索引与其他索引不同,它在索引当中使用了from 和 where 子句,有点像DML语句。下面,我对其进行一个访问:
SQL> set autotrace traceonly explain
SQL> select count(*)
  2  from emp e,dept d
  3  where e.deptno = d.deptno
  4    and d.dname = 'HUFVNQ'
  5  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3013959860

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

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

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

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

|   1 |  SORT AGGREGATE             |               |     1 |     3 |      |          |

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

|*  3 |    BITMAP INDEX SINGLE VALUE| EMP_DNAME_IDX |       |       |
|          |

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


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

   3 - access("E"."SYS_NC00004$"='HUFVNQ')

SQL> select e.*
  2  from emp e,dept d
  3  where e.deptno = d.deptno
  4    and d.dname = 'HUFVNQ'
  5  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2125045483

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

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Ti
me     |

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

|   0 | SELECT STATEMENT             |         |     6 |   162 |     6  (17)| 00:00:01 |

|   1 |  MERGE JOIN                  |         |     6 |   162 |     6  (17)| 00:00:01 |

|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    10 |     2   (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN           | DEPT_PK |     6 |       |     1   (0)| 00:00:01 |

|*  4 |   SORT JOIN                  |         |    30 |   510 |     4  (25)| 00:00:01 |

|   5 |    TABLE ACCESS FULL         | EMP     |    30 |   510 |     3   (0)| 00:00:01 |

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

   2 - filter("D"."DNAME"='HUFVNQ')
   4 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO"="D"."DEPTNO")

SQL> set autotrace off


关于创建位图连接索引有一个先决条件,就是必须关联到另一个表的主建或唯一键。否则会出错,如下:
SQL> drop index emp_dname_idx
  2  /

Index dropped.

SQL> alter table emp drop constraint deptno_fk
  2  /

Table altered.

SQL> alter table dept drop constraint dept_pk
  2  /

Table altered.

SQL> create bitmap index emp_dname_idx on emp(d.dname)
  2  from emp e,dept d
  3  where e.deptno = d.deptno
  4  /
from emp e,dept d
           *
ERROR at line 2:
ORA-25954: 维的主键或唯一约束条件缺失

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle位图索引与B树索引区别与使用方法案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1432.html

Oracle研究中心

关键词:

Oracle位置索引使用案例

OracleB树索引使用教程

Oracle位置与B树索引使用区别