BTree换成BitMap以及2者性能比较

由于2个字典表很大，几乎没有什么修改，之前研发同事创建索引时是默认的BTree，今天我将索引重建成了BitMap类型了，性能有了一定的提醒。

[code]

C:\\Documents and Settings\\whzl>sqlplus fwd/fwd@fwd

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 8月 21 10:28:31 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set autotrace traceonly;
SQL> select * from gmap_offset where lat_int=399780 and lon_int=1164035;


执行计划
----------------------------------------------------------
Plan hash value: 380010065

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

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

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

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

| 0 | SELECT STATEMENT | | 1 | 35 | 4(0) | 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| GMAP_OFFSET | 1 | 35 | 4(0) | 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_GMAP_OFFSET | 1 | | 3(0) | 00:00:01 |

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

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


Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("LAT_INT"=399780 AND "LON_INT"=1164035)


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
1 physical reads
0 redo size
840 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> drop index IDX_GMAP_OFFSET;

索引已删除。

SQL> create bitmap index IDX_GMAP_OFFSET on GMAP_OFFSET (LAT_INT, LON_INT)
2 tablespace USERS
3 pctfree 10
4 initrans 2
5 maxtrans 255
6 storage
7 (
8 initial 7M
9 minextents 1
10 maxextents unlimited
11 );

索引已创建。

SQL> select * from gmap_offset where lat_int=399780 and lon_int=1164035;


执行计划
----------------------------------------------------------
Plan hash value: 562744847

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

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

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

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

| 0 | SELECT STATEMENT | | 1 | 35 | 3(0) | 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | GMAP_OFFSET | 1 | 35 | 3(0) | 00:00:01 |

| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |

|* 3 | BITMAP INDEX SINGLE VALUE | IDX_GMAP_OFFSET | | | | |

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

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


Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("LAT_INT"=399780 AND "LON_INT"=1164035)


统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
1 physical reads
0 redo size
840 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>[/code]