位图索引的一个小测试

总结：
在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 processeddrop 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


                                             
        0        0           
	
				
	
					   
位图索引的一个小测试
	  	   
位图索引的一个应用实例
	  	   
创建分区位图索引测试
	  	   
有关位图连接索引(bitmap join index)的一些测试
	  	   
位图索引的故事
	  	   
oracle的位图索引
	  	   
位图索引的故事
	  	   
位图索引的案例
	  	   
位图索引的应用
	  	   
导航的一个小测试
	  	   
探讨Oracle 的位图索引
	  	   
oracle 位图索引的原理
	  	   
数据库位图索引的优缺点
	  	   
外键上索引作用的一个实际测试
	  	   
位图索引
	  	   
位图索引
	  	   
位图索引
	  	   
位图索引
	     		  
	  	   
也许是突然的自我
	  	   
【LeetCode】 454. 4Sum II
	  	   
Lattice系列FPGA入门相关8(理解SerDes之3)
	  	   
重回博客，开始写笔记。
	  	   
linux下Intellij Idea 14的安装
	  	   
位图索引的一个小测试
	  	   
maven基础知识
	  	   
『微信小程序』优秀教程、轮子、开源项目 资源汇总，长期维护更新中... ... ...
	  	   
一些学习资料
	  	   
使用python爬取《长城》豆瓣影评
	  	   
HTML表格
	  	   
hdu2045  涂色问题
	  	   
Python 图片转字符画 学习笔记
	  	   
Python爬虫爬取NBA数据