【读书笔记】【收获，不止Oracle】位图索引（1）

以下实验，在count(*)的条件下，将位图索引与全表扫描和普通索引进行比较

1.创建T表，投入约300万条的数据

SYS@ orcl>drop table t purge;

Table dropped.

SYS@ orcl>create table t as select * from dba_objects;

Table created.

SYS@ orcl>insert into t select * from t;

50346 rows created.

SYS@ orcl>/   

100692 rows created.

SYS@ orcl>/

201384 rows created.

SYS@ orcl>/

402768 rows created.

SYS@ orcl>/

805536 rows created.

SYS@ orcl>/

1611072 rows created.

SYS@ orcl>update t set object_id=rownum;

3222144 rows updated.

SYS@ orcl>commit;

Commit complete.

2.查看全表扫描的执行计划

SYS@ orcl>set autotrace on;
SYS@ orcl>set linesize 1000
SYS@ orcl>select count(*) from t;

  COUNT(*)
----------
   3222144


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  9772   (2)| 00:01:58 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |  4328K|  9772   (2)| 00:01:58 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
      44150  consistent gets
      38566  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

3.在object_id 列建普通索引，并将该列属性设置为非空，执行查询计划

SYS@ orcl>create index idx_t_obj on t(object_id);

Index created.

SYS@ orcl>alter table T modify object_id not null;

Table altered.

SYS@ orcl>select count(*) from t;

  COUNT(*)
----------
   3222144


Execution Plan
----------------------------------------------------------
Plan hash value: 278572740

---------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |  1705   (4)| 00:00:21 |
|   1 |  SORT AGGREGATE       |           |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_T_OBJ |  4328K|  1705   (4)| 00:00:21 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       7523  consistent gets
          0  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

4.在status列建立位图索引。该列只有有效和失效两种取值，执行查询计划

--位图索引的代价
SYS@ orcl>create bitmap index idx_bitm_t_status on t(status);

Index created.

SYS@ orcl>select count(*) from t;

  COUNT(*)
----------
   3222144


Execution Plan
----------------------------------------------------------
Plan hash value: 4272013625

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |    72   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                   |     1 |            |          |
|   2 |   BITMAP CONVERSION COUNT     |                   |  4328K|    72   (0)| 00:00:01 |
|   3 |    BITMAP INDEX FAST FULL SCAN| IDX_BITM_T_STATUS |       |            |          |
-------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         86  consistent gets
          0  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

总结：

1.逻辑读：全表扫描（44150），普通索引（7523），位图索引（86）。就此种查询需求来看，位图索引更高效。

2.由于位图索引没有设置not null，故能存储空值。