ORACLE BITMAP概要及简单实验

2025-04-15 技术教程

<div><b style="line-height:1.5;">一、bitmap概要</b> </div><div>纲要内容：<br />1.定义，什么是位图索引：就是用位图表示的索引，oracle对于选择度底的列的每个键值建立一个位图，位图中的每一位可能对应多个列，位图中位等于1表示特定的行含有此位图表示的键值。<br />2.查询，由于索引是位图，所以很多很多时候可以对这些索引中的位图进行位运算-（and 和 or),这样的速度明显比b树快（某些情况下）。由于位图索引可以存储null，所以可以直接通过位图索引计数（肯定是准确的）。后面提到的有点和位图的计算方式是直接相关的。<br />3.位图的优点(主要针对dw)：<br /><ul data-front-font-size="14px" style="margin-top:0px;margin-bottom:0px;font-size:14px;"><li>   减少即席查询的相应时间</li><li>和其它类型索引比较，真正节约了索引数据空间</li><li>即使在非常差的硬件上，也可能会有戏剧化的性能提升</li><li>高效的并行DML和LOAD操作。</li><li>生成索引的时候更高效，首先是不排序，其次是占用的空间少（索引空间）。</li><li>可以通过位图索引直接计数。<br /></li></ul><p>4.位图索引的缺点（其它资料），也不好说是缺点</p><ul data-front-font-size="14px" style="margin-top:0px;margin-bottom:0px;font-size:14px;"><li> 不适合选择度底的列</li><li>如果有比较频繁的insert,update等操作，可能导致性能很底下，因为更新索引用的是行锁（可能锁定多行），而不是排它锁。</li><li>可能会溢出，索引数据块难于放下整个索引值，这导致低效。</li></ul><div><br /></div><div><b style="background-color:inherit;">二、主要了解以下位图原理图</b><br style="font-family:微软雅黑;font-size:14px;background-color:#FFFFFF;" /><div style="font-family:微软雅黑;font-size:14px;background-color:#FFFFFF;"><img data-media-type="image" data-attr-org-src-id="8E5B0CED42BD49098911007365EA4D00" data-attr-org-img-file="file:///D:/%E6%9C%89%E9%81%93%E4%BA%91%E7%AC%94%E8%AE%B0/%E6%95%B0%E6%8D%AE%E7%9B%AE%E5%BD%95/zr2095@163.com/9e1562e4eb3f487794640ad476189bf2/65111196479.jpeg" src="file://D:/%E6%9C%89%E9%81%93%E4%BA%91%E7%AC%94%E8%AE%B0/%E6%95%B0%E6%8D%AE%E7%9B%AE%E5%BD%95/zr2095@163.com/9e1562e4eb3f487794640ad476189bf2/65111196479.jpeg" alt="oracle位图索引 - 我行我素 - 逝者如斯夫，不舍昼夜" style="background-color:inherit;cursor:default;display:inline-block;margin-top:8px;max-width:800px;height:auto;" /> </div> <img src="/attachment/201510/8/30208428_144428958888JP.jpeg" width="492" height="376" alt="" /><br /><div style="font-family:微软雅黑;font-size:14px;background-color:#FFFFFF;"><br /><img src="/attachment/201510/8/30208428_144428960179wx.jpeg" width="679" height="513" alt="" /> </div><div style="font-family:微软雅黑;font-size:14px;background-color:#FFFFFF;"><br style="background-color:inherit;" />需要注意的是，这只是个示意图，实际上每个位图的位数并非刚好等于记录数，而是会根据情况来分解，否则对于居多的数据而言，位图未免太大了。</div><br /><br /></div><div><br /></div><b>三、bitmap原理解析</b> </div><div><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;">bitmap的存储结果相对来说，复杂一点。bitmap不存储rowid，rowid存储在每一个bitmap的头部，都存储了rowid的启示位置与结束位置。ORACLE通过自己的内部算法，算出来相应的ROWID。<br style="background-color:inherit;" />位图中的每一位，都记录是否有值。如表的记录是这样存储的：</p><table border="1" cellpadding="2" cellspacing="0" style="font-size:14px;margin-top:10px;margin-bottom:10px;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;"><tbody style="background-color:inherit;"><tr style="background-color:inherit;"><td style="word-break:break-all;border:1px solid #999999;padding:5px 16px 5px 12px;min-height:25px;min-width:25px;height:25px;background-color:inherit;line-height:1.6;">row-value</td></tr><tr style="background-color:inherit;"><td style="word-break:break-all;border:1px solid #999999;padding:5px 16px 5px 12px;min-height:25px;min-width:25px;height:25px;background-color:inherit;line-height:1.6;">male</td></tr><tr style="background-color:inherit;"><td style="word-break:break-all;border:1px solid #999999;padding:5px 16px 5px 12px;min-height:25px;min-width:25px;height:25px;background-color:inherit;line-height:1.6;">female</td></tr><tr style="background-color:inherit;"><td style="word-break:break-all;border:1px solid #999999;padding:5px 16px 5px 12px;min-height:25px;min-width:25px;height:25px;background-color:inherit;line-height:1.6;">female</td></tr><tr style="background-color:inherit;"><td style="word-break:break-all;border:1px solid #999999;padding:5px 16px 5px 12px;min-height:25px;min-width:25px;height:25px;background-color:inherit;line-height:1.6;">male</td></tr></tbody></table><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;">那么对应的bitmap则是这样存储的：</p><table border="1" cellpadding="2" cellspacing="0" width="100%" style="font-size:14px;margin-top:10px;margin-bottom:10px;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;"><tbody style="background-color:inherit;"><tr style="background-color:inherit;"><td style="word-break:break-all;border:1px solid #999999;padding:5px 16px 5px 12px;min-height:25px;min-width:25px;height:25px;background-color:inherit;line-height:1.6;">rowid的启示位置与结束位置</td><td style="word-break:break-all;border:1px solid #999999;padding:5px 16px 5px 12px;min-height:25px;min-width:25px;height:25px;background-color:inherit;line-height:1.6;">rowid的启示位置与结束位置</td></tr><tr style="background-color:inherit;"><td style="word-break:break-all;border:1px solid #999999;padding:5px 16px 5px 12px;min-height:25px;min-width:25px;height:25px;background-color:inherit;line-height:1.6;">male</td><td style="word-break:break-all;border:1px solid #999999;padding:5px 16px 5px 12px;min-height:25px;min-width:25px;height:25px;background-color:inherit;line-height:1.6;">female</td></tr><tr style="background-color:inherit;"><td style="word-break:break-all;border:1px solid #999999;padding:5px 16px 5px 12px;min-height:25px;min-width:25px;height:25px;background-color:inherit;line-height:1.6;">1</td><td style="word-break:break-all;border:1px solid #999999;padding:5px 16px 5px 12px;min-height:25px;min-width:25px;height:25px;background-color:inherit;line-height:1.6;">0</td></tr><tr style="background-color:inherit;"><td style="word-break:break-all;border:1px solid #999999;padding:5px 16px 5px 12px;min-height:25px;min-width:25px;height:25px;background-color:inherit;line-height:1.6;">0</td><td style="word-break:break-all;border:1px solid #999999;padding:5px 16px 5px 12px;min-height:25px;min-width:25px;height:25px;background-color:inherit;line-height:1.6;">1</td></tr><tr style="background-color:inherit;"><td style="word-break:break-all;border:1px solid #999999;padding:5px 16px 5px 12px;min-height:25px;min-width:25px;height:25px;background-color:inherit;line-height:1.6;">0</td><td style="word-break:break-all;border:1px solid #999999;padding:5px 16px 5px 12px;min-height:25px;min-width:25px;height:25px;background-color:inherit;line-height:1.6;">1</td></tr><tr style="background-color:inherit;"><td style="word-break:break-all;border:1px solid #999999;padding:5px 16px 5px 12px;min-height:25px;min-width:25px;height:25px;background-color:inherit;line-height:1.6;">1</td><td style="word-break:break-all;border:1px solid #999999;padding:5px 16px 5px 12px;min-height:25px;min-width:25px;height:25px;background-color:inherit;line-height:1.6;">0</td></tr></tbody></table><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;">由此可见，存储的空间大大的节省了，另外带来的收益就是扫描的BLOCK也大大减少了。</p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;">如果查找性别是male的数据，ORACLE只会去搜索MALE这一列，然后是1的记录，返回即可。</p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;"><br /></p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;">如果是针对BITMAP字段本身做OR,AND这样的查询，那么ORACLE会在BITMAP索引内部，先做一次判断，找出符合结果的，再去计算ROWID，最后给出相应的VALUE，示意图如下：</p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;background-color:#FAFAFC;"><img src="/attachment/201510/8/30208428_1444289696Hga7.gif" width="600" height="176" alt="" /></p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;background-color:#FAFAFC;"><br /></p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;"><strong style="background-color:inherit;">bitmap join index</strong><br style="background-color:inherit;" />bitmap join index，它的特点就是将多张表的JOIN结果，存储在一个索引里面，然后使用BITMAP的形式进行存储。这个对于类似DW那样的多表join效率提高很明显。<br style="background-color:inherit;" />做用3张表join来做测试如下，原来的SQL是这样的：<br style="background-color:inherit;" />test@DB><span style="line-height:1.5;">select wt_cust.company_name,wt_cust.gmt_create</span> </p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;">2   from wt_cust,wt_CUST_EXT ,wt_CUST_BOOK<br style="background-color:inherit;" />3  where wt_cust.id=wt_CUST_EXT.Cust_Id<br style="background-color:inherit;" />4  and wt_CUST_BOOK.Cust_Id=wt_cust.id;</p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;">58 rows selected.</p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;">Elapsed: 00:00:00.01</p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;">Execution Plan<br style="background-color:inherit;" />———————————————————-</p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;">—————————————————————————————————-<br style="background-color:inherit;" />| Id  | Operation                     | Name                          | Rows  | Bytes | Cost (%CPU)|<br style="background-color:inherit;" />—————————————————————————————————-<br style="background-color:inherit;" />|   0 | SELECT STATEMENT              |                               |    54 |  2484 |   179   (0)|<br style="background-color:inherit;" />|   1 |  NESTED LOOPS                 |                               |    54 |  2484 |   179   (0)|<br style="background-color:inherit;" />|   2 |   NESTED LOOPS                |                               |   177 |  7257 |   179   (0)|<br style="background-color:inherit;" /><span style="background-color:inherit;color:#FF0000;">|   3 |    INDEX FULL SCAN            | wt_CUST_BOOK_UK     |   177 |  1062 |     1   (0)|<br style="background-color:inherit;" />|   4 |    TABLE ACCESS BY INDEX ROWID| wt_CUST             |     1 |    35 |     2   (0)|<br style="background-color:inherit;" />|*  5 |     INDEX UNIQUE SCAN         | wt_CUST_PK          |     1 |       |     1   (0)|<br style="background-color:inherit;" />|*  6 |   INDEX RANGE SCAN            | wt_CUST_EXT_CID_IND |     1 |     5 |     0   (0)|</span><br style="background-color:inherit;" />—————————————————————————————————-</p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;">一个3表join，效率很差。如果我们创建BITMAP JOIN INDEX则可以避免这种情况的发生：<br style="background-color:inherit;" />test@DB>CREATE BITMAP INDEX cust_wt_test<br style="background-color:inherit;" />2  ON     wt_cust(wt_cust.company_name)<br style="background-color:inherit;" />3  FROM   wt_cust,wt_CUST_EXT ,wt_CUST_BOOK<br style="background-color:inherit;" />4  WHERE  wt_cust.id=wt_CUST_EXT.Cust_Id<br style="background-color:inherit;" />5  and wt_CUST_BOOK.Cust_Id=wt_cust.id<br style="background-color:inherit;" />6  tablespace test_ind  ;</p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;">Index created.</p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;">Elapsed: 00:00:00.08<br style="background-color:inherit;" />再来看看SQL的执行计划：<br style="background-color:inherit;" />xx@DB>select  wt_cust.company_name,wt_cust.gmt_create<br style="background-color:inherit;" />2   from wt_cust,wt_CUST_EXT ,wt_CUST_BOOK<br style="background-color:inherit;" />3  where wt_cust.id=wt_CUST_EXT.Cust_Id<br style="background-color:inherit;" />4  and wt_CUST_BOOK.Cust_Id=wt_cust.id;</p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;">58 rows selected.</p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;">Elapsed: 00:00:00.00</p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;">Execution Plan<br style="background-color:inherit;" />———————————————————-</p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;">—————————————————————————————<br style="background-color:inherit;" />| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)|<br style="background-color:inherit;" />—————————————————————————————<br style="background-color:inherit;" />|   0 | SELECT STATEMENT             |                   |  1834K|    61M|   219K  (1)|<br style="background-color:inherit;" />|   1 |  TABLE ACCESS BY INDEX ROWID | wt_CUST |  1834K|    61M|   219K  (1)|<br style="background-color:inherit;" />|   2 |   BITMAP CONVERSION TO ROWIDS|                   |       |       |            |<br style="background-color:inherit;" /><span style="background-color:inherit;color:#FF0000;">|   3 |    BITMAP INDEX FULL SCAN    | CUST_WT_TEST      |       |       |            |</span><br style="background-color:inherit;" />—————————————————————————————</p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;">请着重注意红色部分。逻辑读大大降低！！</p><p style="font-size:14px;line-height:21px;white-space:normal;widows:auto;color:#333333;font-family:tahoma, 宋体;text-align:justify;background-color:#FAFAFC;"><br /></p></div>