1、btree 索引通过建表t1 object_id的值没有重复值，而t2 表的object_id的值重复率很高通过实验在t1，t2表的object_id列建立普通索引，来证明普通索引列比较适合列的重复值比较低的列优点：适合键值重复率较低的字段上使用 那么有个B-tree索引我们就像翻书目录一样，直接定位rowid立刻就找到了我们想要的数据，实质减少了I/O操作就提高速度，它有一 个显著特点查询性能与表中数据量无关缺点：不适合键值重复率较高的字段上使用，SQL> create table t1 as select object_id,object_name from dba_objects;Table created.SQL> create table t2 as select mod(object_id,2) object_id,object_name from dba_objects;Table created.SQL> create index ind_t1 on t1(object_id);Index created.SQL> create index ind_t2 on t2(object_id);Index created.收集统计信息：BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'scott', tabname => 't1', estimate_percent =>100, method_opt => 'for all columns size 1', degree => 8, cascade=>TRUE ); END; BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'scott', tabname => 't2', estimate_percent =>100, method_opt => 'for all columns size 1', degree => 8, cascade=>TRUE ); END; SQL> select count(*) from t1 where object_id=1; COUNT(*)---------- 0Execution Plan----------------------------------------------------------Plan hash value: 2587783732----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 5 || ||* 2 | INDEX RANGE SCAN| IND_T1 | 1 | 5 | 1 (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("OBJECT_ID"=1)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size525 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL> select count(*) from t2 where object_id=1; COUNT(*)---------- 36200Execution Plan----------------------------------------------------------Plan hash value: 2800912005--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 3 | 38 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 3 | | ||* 2 | INDEX FAST FULL SCAN| IND_T2 | 36086 | 105K| 38 (0)| 00:00:01 |--------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("OBJECT_ID"=1)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets144 consistent gets 0 physical reads 0 redo size527 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL> drop index ind_t1;Index dropped.SQL> drop index ind_t2;Index dropped.2、位图索引位图索引适合于：列的基数很少，可枚举，重复值很多，数据不会被经常更新，由于一个键值对应很多行（rowid）， 更新索引键值的时候，就会锁定索引，导致其他行不可被修改，阻塞优点：OLAP 例如报表类数据库 重复率高的数据 特定类型的查询例如count、or、and等逻辑操作因为只需要进行位运算即可得到我们需要的结果缺点：不适合重复率低的字段，还有经常DML操作（insert，update，delete），因为位图索引的锁代价极高，修改一个位图索引段影响整个位图段，例如修改一个键值，会影响同键值的多行，所以对于OLTP 系统位图索引基本上是不适用的接着上面的实验，在t1 t2表上建立位图索引SQL> create bitmap index ind_t1 on t1(object_id);Index created.SQL> create bitmap index ind_t2 on t2(object_id);Index created.SQL> select segment_name,bytes from user_segments where segment_name like '%T1%' OR SEGMENT_NAME LIKE '%T2%';SEGMENT_NAME BYTES--------------------------------------------------------------------------------- ----------T1 3145728T2 3145728IND_T1 3145728IND_T2 65536我们可以看出t1表的object_id列没有重复值，而t2表的object_id列重复值很多，建立位图索引的时候，重复值越多，位图索引就越小SQL> drop table t1;SQL> drop table t2;下面我们来看一下，在重复率很高的情况下，位图索引和btree的效率 create table t1 as select object_id,object_type from dba_objects;create table t2 as select object_id,object_type from dba_objects;create index ind_t1 on t1(object_type);create bitmap index ind_t2 on t2(object_type);SQL> select count(*) from t1 where object_type='TABLE';Execution Plan----------------------------------------------------------Plan hash value: 2587783732----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 9 | 5 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 9 || ||* 2 | INDEX RANGE SCAN| IND_T1 | 1678 | 15102 | 5 (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("OBJECT_TYPE"='TABLE')Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size527 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL> select count(*) from t2 where object_type='TABLE';Execution Plan----------------------------------------------------------Plan hash value: 2032664525--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 9 | 1 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 9 | | || 2 | BITMAP CONVERSION COUNT | |1678 | 15102 | 1 (0)| 00:00:01 ||* 3 | BITMAP INDEX SINGLE VALUE| IND_T2 | | | | |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("OBJECT_TYPE"='TABLE')Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size526 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL> select * from t1 where object_type='TABLE';2799 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 634656657--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | |1678 | 23492 | 26 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T1 |1678 | 23492 | 26 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IND_T1 |1678 | | 5 (0)| 00:00:01 |--------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("OBJECT_TYPE"='TABLE')Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets427 consistent gets 0 physical reads 0 redo size 79004 bytes sent via SQL*Net to client 2569 bytes received via SQL*Net from client188 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2799 rows processedSQL> select * from t2 where object_type='TABLE';2800 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 2737179948---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1678 | 23492 | 47(0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID | T2 | 1678 | 23492 | 47(0)| 00:00:01 || 2 | BITMAP CONVERSION TO ROWIDS| | | | | ||* 3 | BITMAP INDEX SINGLE VALUE | IND_T2 | | | | |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("OBJECT_TYPE"='TABLE')Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets235 consistent gets 0 physical reads 0 redo size 79020 bytes sent via SQL*Net to client 2569 bytes received via SQL*Net from client188 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2800 rows processed在等值查找中我们可以看出位图索引的效率依言高于B-tree索引上面实验参考了http://www.itpub.net/thread-1700144-1-1.html
create table t1 as select object_id,mod(object_id,2) id, object_name, object_type From dba_objects;create table t2 as select object_id,mod(object_id,2) id, object_name, object_type From dba_objects;create index ind_type_t1 on t1(object_type);create bitmap index ind_type_t2 on t2(object_type);create index ind_object_id_t1 on t1(object_id);create bitmap index ind_object_id_t2 on t2(object_id);create index ind_id_t1 on t1(id);create bitmap index ind_id_t2 on t2(id);SQL> select * from t1 where object_id in (1,2,10,20,30,50,60,70,40);8 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1020377091-------------------------------------------------------------------------------------------------| Id | Operation | Name| Rows| Bytes | Cost (%CPU)| Time|-------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT || 9 | 369 | 10 (0)| 00:00:01 || 1 | INLIST ITERATOR |||| ||| 2 | TABLE ACCESS BY INDEX ROWID| T1| 9 | 369 | 10 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN | IND_OBJECT_ID_T1 | 9 || 9 (0)| 00:00:01 |-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("OBJECT_ID"=1 OR "OBJECT_ID"=2 OR "OBJECT_ID"=10 OR "OBJECT_ID"=20 OR "OBJECT_ID"=30 OR "OBJECT_ID"=40 OR "OBJECT_ID"=50 OR "OBJECT_ID"=60 OR "OBJECT_ID"=70)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 12 consistent gets 1 physical reads 0 redo size980 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 8 rows processedSQL> select * from t2 where object_id in (1,2,10,20,30,50,60,70,40);8 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 3310774432--------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 9 | 369 | 11 (0)| 00:00:01 || 1 | INLIST ITERATOR | | | | | || 2 | TABLE ACCESS BY INDEX ROWID | T2 | 9 | 369 | 11 (0)| 00:00:01 || 3 | BITMAP CONVERSION TO ROWIDS| | | | | ||* 4 | BITMAP INDEX SINGLE VALUE | IND_OBJECT_ID_T2 | | | | |--------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - access("OBJECT_ID"=1 OR "OBJECT_ID"=2 OR "OBJECT_ID"=10 OR "OBJECT_ID"=20 OR "OBJECT_ID"=30 OR "OBJECT_ID"=40 OR "OBJECT_ID"=50 OR "OBJECT_ID"=60 OR "OBJECT_ID"=70)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 15 consistent gets 1 physical reads 0 redo size980 bytes sent via SQL*Net to client523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 8 rows processed结论：在使用or 的情况下，object_id 重复率比较低的情况下，还是btree效率高一些
SQL> select * From t2 where object_type in ('INDEX','CLUSTER');3805 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 879057093---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3357 |134K|127 (0)| 00:00:02 || 1 | INLIST ITERATOR | | | | | || 2 | TABLE ACCESS BY INDEX ROWID | T2 | 3357 |134K|127 (0)| 00:00:02 || 3 | BITMAP CONVERSION TO ROWIDS| | | | | ||* 4 | BITMAP INDEX SINGLE VALUE | IND_TYPE_T2 | | | | |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - access("OBJECT_TYPE"='CLUSTER' OR "OBJECT_TYPE"='INDEX')Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets349 consistent gets 1 physical reads 0 redo size 198248 bytes sent via SQL*Net to client 3306 bytes received via SQL*Net from client255 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3805 rows processedSQL> select * From t1 where object_type in ('INDEX','CLUSTER');3805 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 918902357--------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3357 | 134K|87 (0)| 00:00:02 || 1 | INLIST ITERATOR | | | || || 2 | TABLE ACCESS BY INDEX ROWID| T1 | 3357 | 134K|87 (0)| 00:00:02 ||* 3 | INDEX RANGE SCAN | IND_TYPE_T1 | 3357 | |11 (0)| 00:00:01 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("OBJECT_TYPE"='CLUSTER' OR "OBJECT_TYPE"='INDEX')Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets610 consistent gets 0 physical reads 0 redo size 198248 bytes sent via SQL*Net to client 3306 bytes received via SQL*Net from client255 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3805 rows processed结论：在使用or的情况下，object_type重复率比较高的表，还是位图索引效率高一些
