25.读书笔记收获不止Oracle之 位图索引
来源:互联网 发布:淘宝客服找不到人 编辑:程序博客网 时间:2024/03/28 18:54
25.读书笔记收获不止Oracle之 位图索引
接下去来看下位图索引。
比较普通索引和位图索引的性能差异。
Drop table t purge;
Create table t as select * fromdba_objects;
SQL> Insert into t select * from t;
90945 rows created.
SQL> Insert into t select * from t;
181890 rows created.
SQL> Insert into t select * from t;
363780 rows created.
SQL> Insert into t select * from t;
727560 rows created.
SQL>commit;
Set autotrace on
Set linesize 1000
Select count(*) from t;
COUNT(*)
----------
1455120
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 426 (1)| 00:00:01 |
| 1| SORT AGGREGATE | | 1 | | |
| 2| TABLE ACCESS FULL| T | 90945 | 426 (1)| 00:00:01 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
2 recursive calls
1 dbblock gets
35408 consistent gets
0 physical reads
877864 redo size
545 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rowsprocessed
1 创建B树索引
进行了全表扫描。然后在object_id列建索引,并设置该列属性为非空,执行COUNT(*)执行计划及性能。
SQL> create index idx_t_obj on t(object_id);
Index created.
SQL> alter table T modify object_id notnull;
Table altered.
SQL> select count(*) from t;
COUNT(*)
----------
1455120
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 426 (1)| 00:00:01 |
| 1| SORT AGGREGATE | | 1 | | |
| 2| TABLE ACCESS FULL| T | 90945 | 426 (1)| 00:00:01 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
8 recursive calls
0 dbblock gets
24402 consistent gets
0 physical reads
0 redosize
545 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2 rows processed
还是走的全表扫描,因为回表造的消耗要比走索引很多,所以都的是全表扫描。
强制索引看下如下:
SQL> select /*+INDEX(t,idx_t_obj)*/ *from t;
1455120 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3019848943
-------------------------------------------------------------------------------------------------
| Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | |90945 | 9M| 1458K (1)| 00:00:57 |
| 1| TABLE ACCESS BY INDEX ROWID BATCHED| T | 90945 | 9M| 1458K (1)| 00:00:57 |
| 2| INDEX FULL SCAN | IDX_T_OBJ | 90945 | | 3234 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 dbblock gets
1555143 consistent gets
3235 physical reads
0 redosize
194065366 bytes sent via SQL*Netto client
1067628 bytes received viaSQL*Net from client
97009 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
1455120 rows processed
3 创建位图
SQL> create bitmap indexidx_bitm_t_status on t(status);
SQL> select count(*) from t;
COUNT(*)
----------
1455120
Execution Plan
----------------------------------------------------------
Plan hash value: 4272013625
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 32 (0)| 00:00:01 |
| 1| SORT AGGREGATE | | 1 | | |
| 2| BITMAP CONVERSION COUNT | | 90945 | 32 (0)| 00:00:01 |
| 3| BITMAP INDEX FAST FULL SCAN|IDX_BITM_T_STATUS | | | |
-------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 dbblock gets
42 consistent gets
36 physical reads
0 redosize
545 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rowsprocessed
不管B树索引怎么样,位图索引代价小了近百倍。
COUNT(*)性能,比不同的B树索引高很多。
当然,只有最合适的技术没有最高级的技术。
- 25.读书笔记收获不止Oracle之 位图索引
- 26.读书笔记收获不止Oracle之 位图索引即席查询
- 27.读书笔记收获不止Oracle之 位图索引更新
- 14.读书笔记收获不止Oracle之 索引
- 【读书笔记】【收获,不止Oracle】位图索引(1)
- 【读书笔记】【收获,不止Oracle】位图索引(2)
- 12.读书笔记收获不止Oracle之 索引分区表
- 15.读书笔记收获不止Oracle之 索引高度
- 16.读书笔记收获不止Oracle之 分区索引
- 17.读书笔记收获不止Oracle之 索引存储列值
- 21.读书笔记收获不止Oracle之 索引回表效率
- 22.读书笔记收获不止Oracle之 索引特性活用
- 23.读书笔记收获不止Oracle之 组合索引
- 24.读书笔记收获不止Oracle之 索引的危害
- 28.读书笔记收获不止Oracle之 函数索引
- 【读书笔记】【收获,不止Oracle】索引组织表
- 1.读书笔记收获不止Oracle之开篇
- 2.读书笔记收获不止Oracle之 意识
- 周志华《Machine Learning》学习笔记(8)--贝叶斯分类器
- 深度探索C++对象模型 【第四章1】
- 24.读书笔记收获不止Oracle之 索引的危害
- javaWeb项目,修改css或者js文件后前台显示没效果
- caioj1099: 线段树(元问题)线性结构求极值和修改
- 25.读书笔记收获不止Oracle之 位图索引
- leetcode练习(43)
- HashMap和Hashtable的区别总结
- Spring MVC解析视图流程
- 26.读书笔记收获不止Oracle之 位图索引即席查询
- 总结非结构化数据分析「十步走」
- 最长回文子串
- 并发编程(7)-阻塞队列
- 27.读书笔记收获不止Oracle之 位图索引更新