在OLTP系统下,如果查询条件是多列,多变的时候使用位图索引,但是在OLAP系统下,不能建立位图索引,
比如说我要修改的是owner='SYS',那么owner='SYS'这一列就会被锁。
a,b,c,d,e
1、a,b,c
2、a,b,d
3、b,c,d
select count(*)
from t1
where owner = 'SYS'
and object_type = 'TABLE'
and status = 'VALID';
--建组合索引。
--在三个列上单独建立索引
create index idx_1 on t1(owner);
create index idx_2 on t1(object_type);
create index idx_3 on t1(status);
执行计划
执行计划
----------------------------------------------------------
Plan hash value: 2712755765
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 174 (0)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 33 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 2343 | 77319 | 174 (0)| 00:00:03 |
|* 3 | INDEX RANGE SCAN | IDX_2 | 4157 | | 11 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS' AND "STATUS"='VALID')
3 - access("OBJECT_TYPE"='TABLE')
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
275 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
drop index idx_1;
drop index idx_2;
drop index idx_3;
--建立位图索引,位图索引是进行AND运算,然后根据位图转换成count
create bitmap index idx_1 on t1(owner);
create bitmap index idx_2 on t1(object_type);
create bitmap index idx_3 on t1(status);
SQL> select count(*)
2 from t1
3 where owner = 'SYS'
4 and object_type = 'TABLE'
5 and status = 'VALID';
执行计划
----------------------------------------------------------
Plan hash value: 467448745
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 33 | | |
| 2 | BITMAP CONVERSION COUNT | | 2343 | 77319 | 3 (0)| 00:00:01 |
| 3 | BITMAP AND | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE| IDX_2 | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE| IDX_1 | | | | |
|* 6 | BITMAP INDEX SINGLE VALUE| IDX_3 | | | | |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("OBJECT_TYPE"='TABLE')
5 - access("OWNER"='SYS')
6 - access("STATUS"='VALID')
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
63 recursive calls
0 db block gets
94 consistent gets
24 physical reads
0 redo size
528 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--假如最后结果:1 0 0 1 1 1 0 1,因为求得是count,只需要数1就可以。
--如果是求*,那么位图转换成ROWID,根据ROWID回表。
执行计划
----------------------------------------------------------
Plan hash value: 1851967648
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2343 | 473K| 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 2343 | 473K| 3 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 3 | BITMAP AND | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE| IDX_2 | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE| IDX_1 | | | | |
|* 6 | BITMAP INDEX SINGLE VALUE| IDX_3 | | | | |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("OBJECT_TYPE"='TABLE')
5 - access("OWNER"='SYS')
6 - access("STATUS"='VALID')
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
424 recursive calls
0 db block gets
384 consistent gets
3 physical reads
0 redo size
202270 bytes sent via SQL*Net to client
1917 bytes received via SQL*Net from client
129 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1915 rows processed