位图索引浅析

最新推荐文章于 2018-08-20 14:59:29 发布

blockheading

最新推荐文章于 2018-08-20 14:59:29 发布

阅读量344

点赞数

分类专栏： Oracle Basic Principle 文章标签：索引位图数据存储

本文链接：https://blog.csdn.net/u013169075/article/details/52974260

版权

Oracle Basic Principle 专栏收录该内容

57 篇文章 0 订阅

订阅专栏

位图索引存储方式如图所示，它的一个索引键值会指向多行数据。性别为X的行指向ROW1,ROW3,ROW4,ROW5诸如此类，位图索引对于相异基数(distinct cardinality)低的数据较为适合，但这个低是相对意义上的低，对这个数字很难进行量化。对于有几千万条记录来说2就是一个低数字，而在一个成千万上亿的记录表中甚至100000都能叫作为低。B Tree索引一般来讲应该是具有高度选择性的，与之相反，位图索引不该具有这么高的选择性，而是一般没有选择性。

实验观察原理：

EODA@PROD1> set echo on
EODA@PROD1> 
EODA@PROD1> create table t   --创建测试表
  2    ( gender not null,
  3  	 location not null,
  4  	 age_group not null,
  5  	 data
  6  	)
  7  	as
  8    select decode( round(dbms_random.value(1,2)),
  9    1, 'M',
 10    2, 'F' ) gender,
 11    ceil(dbms_random.value(1,50)) location,
 12    decode( round(dbms_random.value(1,5)),
 13    1,'18 and under',
 14    2,'19-25',
 15    3,'26-30',
 16    4,'31-40',
 17    5,'41 and over'),
 18    rpad( '*', 20, '*')
 19  from dual connect by level <=100000;

Table created.

EODA@PROD1> 
EODA@PROD1> create bitmap index gender_idx on t(gender);  --创建位图索引

Index created.

EODA@PROD1> create bitmap index location_idx on t(location);  --创建位图索引

Index created.

EODA@PROD1> create bitmap index age_group_idx on t(age_group);  --创建位图索引

Index created.

EODA@PROD1> 
EODA@PROD1> exec dbms_stats.gather_table_stats( user, 'T');

PL/SQL procedure successfully completed.

EODA@PROD1> 
EODA@PROD1> set lines 132
EODA@PROD1> set autotrace traceonly explain
EODA@PROD1> 
EODA@PROD1> select count(*)
  2  from t
  3  where gender = 'M'
  4  and location in ( 1, 10, 30 )
  5  and age_group = '41 and over';

Execution Plan
----------------------------------------------------------
Plan hash value: 320981916

-----------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 	      |     1 |    13 |     9	(0)| 00:00:01 |
|   1 |  SORT AGGREGATE 	      | 	      |     1 |    13 | 	   |	      |
|   2 |   BITMAP CONVERSION COUNT     | 	      |   608 |  7904 |     9	(0)| 00:00:01 |
|   3 |    BITMAP AND		      | 	      |       |       | 	   |	      |
|   4 |     BITMAP OR		      | 	      |       |       | 	   |	      |
|*  5 |      BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       | 	   |	      |
|*  6 |      BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       | 	   |	      |
|*  7 |      BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       | 	   |	      |
|*  8 |     BITMAP INDEX SINGLE VALUE | AGE_GROUP_IDX |       |       | 	   |	      |
|*  9 |     BITMAP INDEX SINGLE VALUE | GENDER_IDX    |       |       | 	   |	      |
-----------------------------------------------------------------------------------------------

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

   5 - access("LOCATION"=1)
   6 - access("LOCATION"=10)
   7 - access("LOCATION"=30)
   8 - access("AGE_GROUP"='41 and over')
   9 - access("GENDER"='M')

/* Oracle先分别读取了5 - access("LOCATION"=1)，6 - access("LOCATION"=10)，7 - access("LOCATION"=30)这三个值的位图
并对这三个位图按位进行逻辑或操作，然后与8 - access("AGE_GROUP"='41 and over')，9 - access("GENDER"='M')进行逻辑与操作
最后BITMAP CONVERSION COUNT统计个数。
 */
 
EODA@PROD1> 
EODA@PROD1> select *      --这个例子解读方法类似
  2  from t
  3  where (( gender = 'M' and location = 20 )
  4  	       or ( gender = 'F' and location = 22 ))
  5  and age_group = '18 and under';

Execution Plan
----------------------------------------------------------
Plan hash value: 1064409070

------------------------------------------------------------------------------------------------
| Id  | Operation		       | Name	       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |	       |   408 | 13872 |    68	 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | T	       |   408 | 13872 |    68	 (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS  |	       |       |       |	    |	       |
|   3 |    BITMAP AND		       |	       |       |       |	    |	       |
|*  4 |     BITMAP INDEX SINGLE VALUE  | AGE_GROUP_IDX |       |       |	    |	       |
|   5 |     BITMAP OR		       |	       |       |       |	    |	       |
|   6 |      BITMAP AND 	       |	       |       |       |	    |	       |
|*  7 |       BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       |	    |	       |
|*  8 |       BITMAP INDEX SINGLE VALUE| GENDER_IDX    |       |       |	    |	       |
|   9 |      BITMAP AND 	       |	       |       |       |	    |	       |
|* 10 |       BITMAP INDEX SINGLE VALUE| LOCATION_IDX  |       |       |	    |	       |
|* 11 |       BITMAP INDEX SINGLE VALUE| GENDER_IDX    |       |       |	    |	       |
------------------------------------------------------------------------------------------------

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

   4 - access("AGE_GROUP"='18 and under')
   7 - access("LOCATION"=22)
   8 - access("GENDER"='F')
  10 - access("LOCATION"=20)
  11 - access("GENDER"='M')
  
--位图适用于读密集型的环境，因为一个位图索引键条目会只想很多行的数据。

--参考来源《Oracle编程艺术深入理解数据库体系结构（第三版）》