ORACLE BITMAP概要及简单实验

发布时间：2020-08-06 19:11:05 作者：Davis_itpub
来源：ITPUB博客阅读：191

<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>

ORACLE BITMAP概要及简单实验

相关阅读