郑重声明：由于本人水平有限，如有不对的地方请大神指正不甚感激（327568824@qq.com）
创建T6表，将dba_objects里的内容全部添加进去。
SQL> createtable t6 as select * from dba_objects;
Table created.
再多添加几次同样的内容
SQL> insert into t6 select * from t6;
71747 rowscreated.
SQL> /
143494 rowscreated.
SQL> /
286988 rowscreated.
给T6添加位图索引
SQL> create bitmap index t6_bit_ind ont6(object_type);
Index created.
将同样的内容添复制到T7表中
SQL> createtable t7 as select * from t6;
Table created.
给T7添加B树索引
SQL>createindex t7_ind on t7(object_type);
Index created.
我们可以看到B树索引比位图索引占用的空间要大。
SQL> select segment_name,bytes from user_segmentsWHERE segment_name='T6_BIT_IND' OR segment_name='T7_IND';
SEGMENT_NAME BYTES
---------------------------------------------------------------------------
T6_BIT_IND 327680
T7_IND 13631488
SQL> select count(*) from t6 whereobject_type='TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value:1829406406
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | BITMAP CONVERSION COUNT | | 21221 | 227K| 2 (0)| 00:00:01 |
|* 3 | BITMAP INDEX SINGLE VALUE| T6_BIT_IND | | | | |
------------------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_TYPE"='TABLE')
Note
-----
- dynamic sampling used for this statement(level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redosize
528 bytes sent via SQL*Net to client
523 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 count(*) from t7 whereobject_type='TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value:2455506375
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 55 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX RANGE SCAN| T7_IND | 18954 | 166K| 55 (0)| 00:00:01 |
----------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
45 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
523 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 count(*) from t7 whereobject_type='TABLE' or object_type='INDEX';
Execution Plan
----------------------------------------------------------
Plan hash value:2567312643
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 144 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | INLIST ITERATOR | | | | | |
|* 3 | INDEX RANGE SCAN| T7_IND | 52020 | 457K| 144 (0)| 00:00:02 |
-----------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_TYPE"='INDEX' OR"OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
128 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
523 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 count(*) from t6 where object_type='TABLE' or object_type='INDEX';
Execution Plan
----------------------------------------------------------
Plan hash value:1642841110
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | INLIST ITERATOR | | | | | |
| 3 | BITMAP CONVERSION COUNT | | 53540 | 470K| 5 (0)| 00:00:01 |
|* 4 | BITMAP INDEX SINGLE VALUE| T6_BIT_IND | | | | |
-------------------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
4 - access("OBJECT_TYPE"='INDEX'OR "OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
从上面4个例子，我们看出索引对重复数据上进行的and,or操作比B树索引更优。
原因是由于位图索引的每个键值中保存了该键值在那些记录中存在的信息，当Oracle要对不同键值进行AND、OR操作时，Oracle 只需通过对这些信息在各个行上的分布（实际上是对0、1的位运算）进行计算，即可得出最终的结果。
我认为，位图索引应该适用于下面两种情况：
● 适合OLAP数据库
● 重复率高的数据；
● 使用OR\AND这类逻辑操作
位图索引使用的范围非常有限，特别是对于一些OLTP 系统，位图索引是不适合的。 在OLTP 系统中，通常有大量的DML 操作，比如update、insert、delete。在这种情况下，位图索引会引起副作用。 NOT 逻辑运算，就可以很容易解决那些B树索引无法解决的问题，而且速度非常快。
实际上，位图索引非常不适合有大量“写”操作的数据库，这和它的数据存储机制有关。位图索引的数据存储机制，它并不像B 树索引那样，每个键值中存放一个唯一的ROWID，对应表中的唯一记录。
在每一个位图索引键值中，可能都对应很多条表记录，这样就有一个问题：当一个会话修改了一条记录信息时，它将更新索引键值，而一个索引键值中又存储了很多行记录信息，结果就是，用户修改一行记录，会将对应的位图索引键值加锁。
会话1：
SQL>select distinct sid from v$mystat;
SID
----------
60
SQL>create table t8(id int,name varchar(20));
Table created.
SQL>create bitmap index t8_bit_inx on t8(id);
Index created.
SQL>insert into t8 values(1,'a');
1 row created.
SQL>insert into t8 values(1,'b');
1 row created.
SQL>commit;
Commit complete.
SQL>select * from t8;
ID NAME
---------- --------------------
1 a
1 b
SQL>update t8 set id=2 where name='1';
会话二：
SQL>select distinct sid from v$mystat;
SID
----------
66
SQL> update t8 set id=2 where name='b';
我们可以看到操作被阻塞了，查看下v$lock
SQL>select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in(60,66);
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- -------------------- ---------- ---------- ----------
60 AE 100 0 4 0 6337 2
66 TX 524300 3696 0 4 280 0
66 AE 100 0 4 0 322 2
60 TO 5124 1 3 0 3918 2
60 TM 87038 0 3 0 360 2
66 TM 87038 0 3 0 280 2
60 TX 524300 3696 6 0 351 1
66 TX 65567 3549 6 0 280 2
8 rows selected
从v$lock 视图中可以看到，会话60持有一个会话66所需要的锁，所以会话66此时会被阻塞。
OLTP 系统是一个在线交易频繁的系统，并发非常大，而并发DML操作正是位图索
引的硬伤，所以对于OLTP 系统，位图索引基本上是不适合的。
上面的演示可以给位图索引所使用的范围下这样明确的界定：
● 适合海量数据的OLAP 或者数据仓库系统；
● 适合特定的SQL 操作；
● 不适合OLTP 系统。
6922
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
