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树索引高很多。

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!