位图索引用来存储有单个索引码条目的许多行的指针,而在B*Tree 结构中,索引码和表中的行是一一对应,在位图索引中,索引的条目是非常少的,每个条目指向许多行,在B*Tree索引中,是一对一的,一个索引条目指向一行。
创建表EMP,表中job_title有许多重复值
SQL> create table emp as select e.*,j.job_title
2 from employees e, jobs j where e.job_id = j.job_id;
在EMP表中的job_title列上创建位图索引
SQL> create bitmap index bit_job_tt on emp(job_title);
创建表EMP,表中job_title有许多重复值
SQL> create table emp as select e.*,j.job_title
2 from employees e, jobs j where e.job_id = j.job_id;
在EMP表中的job_title列上创建位图索引
SQL> create bitmap index bit_job_tt on emp(job_title);
索引已创建。
已用时间: 00: 00: 00.26
SQL>
我们查询 job_title为'Stock Clerk'的EMP
SQL> select count(*) from emp where job_title='Stock Clerk';
SQL>
我们查询 job_title为'Stock Clerk'的EMP
SQL> select count(*) from emp where job_title='Stock Clerk';
已用时间: 00: 00: 00.12
执行计划
----------------------------------------------------------
Plan hash value: 416691478
----------------------------------------------------------
Plan hash value: 416691478
--------------------------------------------------------------------------------
------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
| Time |
--------------------------------------------------------------------------------
------------
------------
| 0 | SELECT STATEMENT | | 1 | 19 | 1 (0)
| 00:00:01 |
| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 19 |
| |
| |
| 2 | BITMAP CONVERSION COUNT | | 20 | 380 | 1 (0)
| 00:00:01 |
| 00:00:01 |
|* 3 | BITMAP INDEX FAST FULL SCAN| BIT_JOB_TT | | |
| |
| |
--------------------------------------------------------------------------------
------------
------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("JOB_TITLE"='Stock Clerk')
Note
-----
- dynamic sampling used for this statement (level=2)
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
9 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
423 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我们看到执行计划,使用了位图索引,直接从位图索引中得到答案、
位图索引的存储结构
Value/Row 1 2 3 4 5 6 7 8 9 10 11 12 13 14
ANALYST 0 0 0 0 0 0 0 1 0 1 0 0 1 0
CLERK 1 0 0 0 0 0 0 0 0 0 1 1 0 1
MANAGER 0 0 0 1 0 1 1 0 0 0 0 0 0 0
PRESIDENT 0 0 0 0 0 0 0 0 1 0 0 0 0 0
SALESMAN 0 1 1 0 1 0 0 0 0 0 0 0 0 0
其中: 1代表符合,0不符合
那么在 select count(*) from emp where job_title='Stock Clerk'查询时,就在位图索引中检索值为1的行。可想,这个速度是很快的
何时使用位图索引呢? 在低基数的数据库中,位图索引时最合适的,在行中的一列中,此列的不同值占总行数的比例越小,此列最适合使用位图索引。
SQL> l
1* select distinct job_title from emp
SQL> /
JOB_TITLE
-----------------------------------
Accounting Manager
Programmer
Public Relations Representative
Purchasing Clerk
Sales Representative
Administration Vice President
Marketing Representative
Stock Manager
Administration Assistant
Finance Manager
President
-----------------------------------
Accounting Manager
Programmer
Public Relations Representative
Purchasing Clerk
Sales Representative
Administration Vice President
Marketing Representative
Stock Manager
Administration Assistant
Finance Manager
President
JOB_TITLE
-----------------------------------
Purchasing Manager
Human Resources Representative
Accountant
Shipping Clerk
Stock Clerk
Marketing Manager
Public Accountant
Sales Manager
-----------------------------------
Purchasing Manager
Human Resources Representative
Accountant
Shipping Clerk
Stock Clerk
Marketing Manager
Public Accountant
Sales Manager
已选择19行。
SQL> select count(*) from emp;
SQL> select count(*) from emp;
COUNT(*)
----------
107
----------
107
已用时间: 00: 00: 00.02
例如在EMP表中,job_title不同值有17个,总行数为107 17/107=.158878505 占15%左右 所以该列最适合使用位图索引了
位图索引在集中读取的环境中工作是很好的,但在集中并发写入的环境中极度的糟糕。
原因是单个位图索引码指向许多行,如果一个会话修改数据,那么索引条目指向的所有行都被锁定。在位图索引条目中,不能锁定单个位,而是锁定整个位图。严重抑制并发性。
例如在EMP表中,job_title不同值有17个,总行数为107 17/107=.158878505 占15%左右 所以该列最适合使用位图索引了
位图索引在集中读取的环境中工作是很好的,但在集中并发写入的环境中极度的糟糕。
原因是单个位图索引码指向许多行,如果一个会话修改数据,那么索引条目指向的所有行都被锁定。在位图索引条目中,不能锁定单个位,而是锁定整个位图。严重抑制并发性。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7551038/viewspace-617656/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7551038/viewspace-617656/