博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
用示例说明BitMap索引的效率要优于B-Tree索引
阅读量:6173 次
发布时间:2019-06-21

本文共 6688 字,大约阅读时间需要 22 分钟。

一、实验说明:

     操作系统:rhel 5.4 x86

     数据库:Oracle 11g R2

二、操作步骤:

    首先创建一张t_btree表,并建立B-Tree索引,索引键是status:

1 SQL> create table t_btree as select * from dba_objects;2 3 Table created.4 5 SQL> create index status_btree on t_btree(status);6 7 Index created.

   执行两次下面的查询语句,并显示执行计划:

1 SQL> set autotrace traceonly; 2 SQL> select count(*) from t_btree where status='VALID'; 3  4  5 Execution Plan 6 ---------------------------------------------------------- 7 Plan hash value: 2400455617 8  9 --------------------------------------------------------------------------------------10 | Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |11 --------------------------------------------------------------------------------------12 |   0 | SELECT STATEMENT      |          |       1 |       5 |      49   (0)| 00:00:01 |13 |   1 |  SORT AGGREGATE       |          |       1 |       5 |          |         |14 |*  2 |   INDEX FAST FULL SCAN| STATUS_BTREE | 74307 |     362K|      49   (0)| 00:00:01 |15 --------------------------------------------------------------------------------------16 17 Predicate Information (identified by operation id):18 ---------------------------------------------------19 20    2 - filter("STATUS"='VALID')21 22 Note23 -----24    - dynamic sampling used for this statement (level=2)25 26 27 Statistics28 ----------------------------------------------------------29      32  recursive calls30       0  db block gets31     261  consistent gets32     458  physical reads33       0  redo size34     424  bytes sent via SQL*Net to client35     419  bytes received via SQL*Net from client36       2  SQL*Net roundtrips to/from client37       0  sorts (memory)38       0  sorts (disk)39       1  rows processed40 41 SQL> select count(*) from t_btree where status='VALID';42 43 44 Execution Plan45 ----------------------------------------------------------46 Plan hash value: 240045561747 48 --------------------------------------------------------------------------------------49 | Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |50 --------------------------------------------------------------------------------------51 |   0 | SELECT STATEMENT      |          |       1 |       5 |      49   (0)| 00:00:01 |52 |   1 |  SORT AGGREGATE       |          |       1 |       5 |          |         |53 |*  2 |   INDEX FAST FULL SCAN| STATUS_BTREE | 74307 |     362K|      49   (0)| 00:00:01 |54 --------------------------------------------------------------------------------------55 56 Predicate Information (identified by operation id):57 ---------------------------------------------------58 59    2 - filter("STATUS"='VALID')60 61 Note62 -----63    - dynamic sampling used for this statement (level=2)64 65 66 Statistics67 ----------------------------------------------------------68       0  recursive calls69       0  db block gets70     180  consistent gets71       0  physical reads72       0  redo size73     424  bytes sent via SQL*Net to client74     419  bytes received via SQL*Net from client75       2  SQL*Net roundtrips to/from client76       0  sorts (memory)77       0  sorts (disk)78       1  rows processed

   接着创建跟t_btree一样的表t_bmap,并创建BitMap索引。

1 SQL> create table t_bmap as select * from dba_objects;2 3 Table created.4 5 SQL> create bitmap index status_bmap on t_bmap(status);6 7 Index created.

   同样执行之前的语句两次:

1 SQL> select count(*) from t_bmap where status='VALID'; 2  3  4 Execution Plan 5 ---------------------------------------------------------- 6 Plan hash value: 516980546 7  8 --------------------------------------------------------------------------------------------- 9 | Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |10 ---------------------------------------------------------------------------------------------11 |   0 | SELECT STATEMENT          |         |      1 |      5 |      3   (0)| 00:00:01 |12 |   1 |  SORT AGGREGATE           |         |      1 |      5 |         |        |13 |   2 |   BITMAP CONVERSION COUNT     |         | 62928 |    307K|      3   (0)| 00:00:01 |14 |*  3 |    BITMAP INDEX FAST FULL SCAN| STATUS_BMAP |        |        |         |        |15 ---------------------------------------------------------------------------------------------16 17 Predicate Information (identified by operation id):18 ---------------------------------------------------19 20    3 - filter("STATUS"='VALID')21 22 Note23 -----24    - dynamic sampling used for this statement (level=2)25 26 27 Statistics28 ----------------------------------------------------------29      32  recursive calls30       0  db block gets31      72  consistent gets32     266  physical reads33       0  redo size34     424  bytes sent via SQL*Net to client35     419  bytes received via SQL*Net from client36       2  SQL*Net roundtrips to/from client37       0  sorts (memory)38       0  sorts (disk)39       1  rows processed40 41 SQL> select count(*) from t_bmap where status='VALID';42 43 44 Execution Plan45 ----------------------------------------------------------46 Plan hash value: 51698054647 48 ---------------------------------------------------------------------------------------------49 | Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |50 ---------------------------------------------------------------------------------------------51 |   0 | SELECT STATEMENT          |         |      1 |      5 |      3   (0)| 00:00:01 |52 |   1 |  SORT AGGREGATE           |         |      1 |      5 |         |        |53 |   2 |   BITMAP CONVERSION COUNT     |         | 62928 |    307K|      3   (0)| 00:00:01 |54 |*  3 |    BITMAP INDEX FAST FULL SCAN| STATUS_BMAP |        |        |         |        |55 ---------------------------------------------------------------------------------------------56 57 Predicate Information (identified by operation id):58 ---------------------------------------------------59 60    3 - filter("STATUS"='VALID')61 62 Note63 -----64    - dynamic sampling used for this statement (level=2)65 66 67 Statistics68 ----------------------------------------------------------69       0  recursive calls70       0  db block gets71       6  consistent gets72       0  physical reads73       0  redo size74     424  bytes sent via SQL*Net to client75     419  bytes received via SQL*Net from client76       2  SQL*Net roundtrips to/from client77       0  sorts (memory)78       0  sorts (disk)79       1  rows processed

   从上面的查询中,我们可以得到,分别给两张内容一样的表做查询的时候,在执行第二次的时候是属于软解析:

   从一致性读上比较,B-Tree索引的consistent gets是180,BitMap的是6;

   从Cost的消耗上看,B-Tree索引的COST是49,而BitMap的是3。

   在索引键是高重复率键值(status)的时候情况下BitMap索引的效率要优于B-Tree索引。

转载地址:http://ckqba.baihongyu.com/

你可能感兴趣的文章
Linux设备模型(热插拔、mdev 与 firmware)【转】
查看>>
Android开发笔记第二篇(Android 手机概念)
查看>>
js隐藏与显示回到顶部按钮
查看>>
hdu4496 D-City(扭转和支票托收啊 )
查看>>
数据挖掘 | 数据理解和预处理
查看>>
关于大数据你必须了解的几个关键词!
查看>>
在Kali Linux中更改GRUB2背景的5种方式
查看>>
如何把Windows 10的“便笺”按钮从操作中心挪到开始菜单和桌面
查看>>
19 个必须知道的 Visual Studio 快捷键
查看>>
如何在Ubuntu命令行下管理浏览器书签
查看>>
《大数据分析原理与实践》一一2.1 大数据分析模型建立方法
查看>>
《 自动化测试最佳实践:来自全球的经典自动化测试案例解析》一一2.7 测试套件和类型...
查看>>
8月18日云栖精选夜读:阿里视频云最强转码技术揭秘:窄带高清原理解析+用户接入指南...
查看>>
涨姿势:工业物联网与大数据融合的四个重点
查看>>
社会学视角下的大数据方法论及其困境
查看>>
《云计算:原理与范式》一1.7 平台即服务供应商
查看>>
百度成立“百度搜索公司”:固本拓新驱动生态裂变
查看>>
宇宙风暴?才怪!瑞典暗指俄罗斯黑客攻击航空控制系统
查看>>
系统进程管理工具Process Explorer
查看>>
富士通仍执着SPARC架构芯片 将坚持推新
查看>>