25.读书笔记收获不止Oracle之 位图索引

接下去来看下位图索引。

比较普通索引和位图索引的性能差异。

Drop table t purge;

Create table t as select * fromdba_objects;

sql> Insert into t select * from t;

90945 rows created.

sql> Insert into t select * from t;

181890 rows created.

sql> Insert into t select * from t;

363780 rows created.

sql> Insert into t select * from t;

727560 rows created.

sql>commit;

Set autotrace on

Set linesize 1000

Select count(*) from t;

COUNT(*)

----------

1455120

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

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

2recursive calls

1 dbblock gets

35408 consistent gets

0physical reads

877864 redo size

545 bytes sent via sql*Net to client

551 bytes received via sql*Net from client

2sql*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1 rowsprocessed

1创建B树索引

进行了全表扫描。然后在object_id列建索引，并设置该列属性为非空，执行COUNT(*)执行计划及性能。

sql> create index idx_t_obj on t(object_id);

Index created.

sql> alter table T modify object_id notnull;

Table altered.

sql> select count(*) from t;

COUNT(*)

----------

1455120

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

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

8recursive calls

0 dbblock gets

24402 consistent gets

0physical reads

0 redosize

545 bytes sent via sql*Net to client

551 bytes received via sql*Net from client

2sql*Net roundtrips to/from client

2sorts (memory)

0sorts (disk)

2rows processed

还是走的全表扫描，因为回表造的消耗要比走索引很多，所以都的是全表扫描。

强制索引看下如下：

sql> select /*+INDEX(t,idx_t_obj)*/ *from t;

1455120 rows selected.

Execution Plan

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

Plan hash value: 3019848943

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

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

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

| 0| SELECT STATEMENT | |90945 | 9M| 1458K(1)| 00:00:57 |

| 1| TABLE ACCESS BY INDEX ROWID BATCHED| T | 90945 | 9M|1458K (1)| 00:00:57 |

| 2| INDEX FULL SCAN | IDX_T_OBJ | 90945 | |3234 (1)| 00:00:01 |

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

Statistics

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

1recursive calls

0 dbblock gets

1555143 consistent gets

3235 physical reads

0 redosize

194065366 bytes sent via sql*Netto client

1067628 bytes received viasql*Net from client

97009 sql*Net roundtrips to/fromclient

0sorts (memory)

0sorts (disk)

1455120 rows processed

3创建位图

sql> create bitmap indexidx_bitm_t_status on t(status);

sql> select count(*) from t;

COUNT(*)

----------

1455120

Execution Plan

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

Plan hash value: 4272013625

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

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

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

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

| 1| SORT AGGREGATE | | 1 | | |

| 2| BITMAP CONVERSION COUNT | | 90945 |32 (0)| 00:00:01 |

| 3| BITMAP INDEX FAST FULL SCAN|IDX_BITM_T_STATUS | | | |

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

Statistics

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

1recursive calls

0 dbblock gets

42 consistent gets

36 physical reads

0 redosize

545 bytes sent via sql*Net to client

551 bytes received via sql*Net from client

2sql*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

1 rowsprocessed

不管B树索引怎么样，位图索引代价小了近百倍。

COUNT(*)性能，比不同的B树索引高很多。

当然，只有最合适的技术没有最高级的技术。