27.读书笔记收获不止Oracle之 位图索引更新

27.读书笔记收获不止Oracle之 位图索引更新

新建立一个会话，查看会话号：

SQL> select sid from v$mystat whererownum=1;

      SID

----------

           42

关于表t，查看上一篇

SQL> insert into t(name_id,gender,location,age_group,data) values(100001,'m',45,'child',rpad('*',20,'*'));

1 row created.

不进行提交。

 

然后新开一个会话

SQL> select sid from v$mystat where rownum=1;

      SID

----------

           72

在新会话中，插入一行

SQL> insert into t(name_id,gender,location,age_group,data) values(100002,'m',46,'child',rpad('*',20,'*'));

导致直接卡主。

 

再换一个会话链接

SQL> select sid from v$mystat where rownum=1;

      SID

----------

           24

插入如下：

SQL>insert into t(name_id,gender,location,age_group,data)values (100003,'f',47,'middle_age',rpad('*',20,'*'));

可以插入。

 

可以发现，gender列仅是m和f两种取值。某SESSSION插入该表的记录是m，其他任何SESSION与m相关的记录就不能插入。

此外，DELETE和UPDATE都不能更新位图索引所在的列。

 

1.  位图索引使用场景

位图在即席查询方面独领风骚，但是对更新确实一场灾难。

位图索引使用两个条件：1、位图索引列大量重复，2、表极少更新。

 

1.1         重复度低情况

Drop table t purge;

Create table t as select * fromdba_objects;

Insert into t select * from t;

Insert into t select * from t;

Insert into t select * from t;

Update t set object_id=rownum;

Commit;

然后建立位图索引

SQL>create bitmap indexidx_bit_object_id on t(object_id);

SQL>set autotrace traceonly

SQL>select count(*) from t;

Execution Plan

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

Plan hash value: 2966233522

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

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

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

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

|   1|  SORT AGGREGATE    |     |   1 |           |  |

|   2|   TABLE ACCESS FULL| T       |90945 |   426   (1)| 00:00:01 |

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

 

 

Statistics

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

             1 recursive calls

             0  dbblock gets

     12411  consistent gets

             0 physical reads

             0  redosize

           544  bytes sent via SQL*Net to client

           551  bytes received via SQL*Net from client

             2 SQL*Net roundtrips to/from client

             0 sorts (memory)

             0 sorts (disk)

             1  rowsprocessed

在OBJECT_ID列建了位图索引后，ORACLE不选择这个索引而走全表扫描。

强制走索引，看看：

SQL> select /*+index(t,idx_bit_object_id)*/ count(*) from t;

 

 

Execution Plan

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

Plan hash value: 2130576087

 

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

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

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

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

|   1|  SORT AGGREGATE     |                     |         1|                    |        |

|   2|   BITMAP CONVERSION COUNT|                        | 90945 |          2540   (1)| 00:00:01 |

|   3|    BITMAP INDEX FULL SCAN|IDX_BIT_OBJECT_ID |                |                  |        |

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

 

 

Statistics

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

             1 recursive calls

             0  dbblock gets

      2536  consistent gets

      2535  physical reads

             0  redosize

           544  bytes sent via SQL*Net to client

           551  bytes received via SQL*Net from client

             2 SQL*Net roundtrips to/from client

             0 sorts (memory)

             0 sorts (disk)

             1  rowsprocessed

 

发现使用这个位图索引后，代价比全表扫描要高很多。

但是全表扫描的逻辑读比位图索引扫描大很多。

当然 COST代价是更准确的，因为除了IO还有CPU等开销。

1.2         真相

加入t表有4个字段，分别是ID、NAME、SEX和STATUS。SEX仅为男或女。

如下图1：

当在SEX列建立位图索引时候，

索引建值只有三个不同的取值，

位图索引存储的是比特位值，例如SEX性别列只有男，整个位图索引的大小大致等于行数乘以这1个比特。位图索引在重复度很高时，体积非常小，COUNT(*) 统计非常快。

           如果对OBJECT_ID这个不重复列建位图索引，那么位图索引大小瞬间增大几百万倍。从中获取条数肯定没有优势了。