Oracle关于位图索引的创建与应用

古城微笑少年丶 2022-05-29 05:38 282阅读 0赞

# [Oracle关于位图索引的创建与应用][Oracle] #

标签： [Oracle][Oracle 1] [位图索引][Link 1]

2016年03月13日 10:55:38 3730人阅读  评论(0)  收藏  举报

![category_icon.jpg][]  分类：

数据库（10） ![arrow_triangle_20_down.jpg][]  oracle（12） ![arrow_triangle_20_down.jpg][]

1)创建  
  
　　CREATE BITMAP INDEX index\_name ON normal\_index\_creation\_clause;  
  
　　Oracle创建一系列的位图，每个位图都与一个特殊的值有关。例如，如果在某一个字段上创建一个位图索引，这个字段上的值有2种，1个是'East'1个是'Central'，那没就建立有2个位图，1个用于'East'1个用于'Central'。  
  
  
　　如果索引建立于多个字段上，那么每种可能的组合都必须有一个位图。  
  
  
　　2)位图索引结构  
  
　　一个规则的B\*tree索引是根据ROWID作为行的关键值来成对，再将这些对整理放置到B\*tree结构中。ROWID作为一个指向一行的指针。位图索引有一个非常不一样的结构：ROWID并不直接存储：每个不同的值有一个它们自己的位图。(这就是为什么位图索引通常建立在独特值较少的列上)。  
  
  
　　位图的每个位置映射到一个可能的ROWID上，位图上每个位置的内容用于表示该行特定的值是否在位图列中。所以，位图的每个位置存储特殊行和相关ROWID的信息。如果该ROWID的行的值匹配，则该特殊rowid位置存储为"1"，否则储存为"0"。Oracle也能够压缩位图存储。  
  
  
　　3)何时使用位图索引  
  
  
　　- 该字段具有较低的集的势：独特值较少  
  
  
　　- 位图索引在具有冗长WHERE条件的复杂查询或聚合查询(包含SUM，COUNT或其他聚合函数)中特别有帮助。  
  
  
　　- 表中有大量的行(100万行有1万个独特值可以接收使用位图索引)  
  
  
　　- 表上有频繁复杂查询  
  
  
　　- 数据库环境为数据仓库(DSS系统)。由于位图索引的加锁形式，所以它不大适合联机事务处理(OLTP)环境。不能单独锁定位图的其中一个位置。  
  
  
　　位图被锁定的最小数量为1个位图段，它可以达到半个数据块的大小。改变一行的值将会导致整个位图段被锁，同时锁住了其他的行。  
  
  
　　这对于用户如果有大量的UPDATE，INSERT，DELETE语句及其不利。但对于数据是批量导入或更新则没有问题，如在一个数据仓库系统中。  
  
  
　　- 位图连接索引是9i引入的一个新方法，可以避免在运行时的连接操作，因为在索引创建时位图索引已经基于了连接。  
  
  
　　BJI是一个在空间上减少选择数据量的有效方法。这些数据导致的连接操作和限制都永久地存储在BJI中。连接条件是一个相对的内部连接，维度表的主键和事实表的外键。  
  
  
　　4)限制条件  
  
  
　　-------------------  
  
  
　　- 位图索引在Trusted Oracle中不支持  
  
  
　　- 不能被规则优化器(RBO)使用  
  
  
　　- 不能用于分区表的全局索引  
  
  
　　- 位图索引不支持build或rebuild的ONLINE选项，在10.2中，只有位图连接索引不支持，普通位图索引可以指定ONLINE选项。  
  
  
　　- 对于有直接装载的位图索引，不提供SORTED\_INDEX标记  
  
  
　　- 位图索引不能用于完整性检查  
  
  
　　- 位图索引不能定义为UNIQUE  
  
  
　　- 9i之前，不能在索引组织表上创建位图索引，9i开始支持在索引组织表上创建位图索引：在IOT上创建位图索引要求有一个映射表。  
  
  
　　- 不能给域索引指定BITMAP  
  
  
　　5)相对B\*tree索引的优势  
  
  
　　-------------------------  
  
  
　　- 在大量复杂查询中节省响应时间  
  
  
　　- 对于常规的B\*tree索引，有效地节省了空间使用  
  
  
　　a)在某一个字段上只有少量的独特值：  
  
  
　　如果在一个具有唯一性的字段上建立位图索引，那么它要求的空间大大超过B\*tree索引。然而，对于每个值都重复了成百上千次，位图索引通常小于常规B\*tree索引的25%。位图自身采用压缩格式存储。  
  
  
　　b)如果多个列被索引  
  
  
　　相对于多列(或连接)的B\*tree索引，位图索引节约了相当可观的存储。在一个只有B\*tree索引的数据库中，必须要预先评估在当个查询中可能访问到的列，然后在这些列上创建组合索引。  
  
  
　　这类索引不仅需要大量的空间，而且要求要有一定的顺序，如一个B\*tree索引建立在(MARITAL\_STATUS,REGION,GENDER)字段上，查询只访问REGION,GENDER时，索引是不起效果的。为了在数据库中充分地使用索引，必须在这些列上变换另一种排列顺序建立索引。举个简单例子，在一个较低的集的势的3个列中，那么可能就存在有6种位置关系不同的组合索引。需要在磁盘空间和性能之前权衡考虑创建何种位置顺序的索引。位图索引解决了这个进退两难问题。位图索引在查询执行过程中可以有效地组合，所以3个较小的单个字段的位图索引可以完成6种3个字段组合的B\*tree索引的工作。  
  
  
　　- 非常高效的并行DML和装载  
  
  
　　位图索引在数据仓库应用中很有效率，但是不适合在OLTP应用系统中使用，因为有大量同时发生的INSERT，UPDATE,DELETE操作。索引直到每个DML操作完成之后才进行维护。例如，如果插入1000行数据，插入的行将放置在排序缓冲区中，然后更新批量更新所有的1000条索引条目。(这就是为什么SORT\_AREA\_SIZE需要设置为一个较优性能的值，用于位图索引的插入和更新)。所以，每个DML操作，位图段只更新一次，即使段中有多行变更时。  
  
  
　　- 行包含null值(见"位图索引和NULLs")  
  
  
　　6)使用小贴士  
  
  
　　------------------  
  
  
　　- 在所有可能非空的字段上声明NOT NULL约束，可以减少存储需求，因为位图不会有NULL值。  
  
  
　　- 使用固定长度的数据格式可以减少存储需求。  
  
  
　　- 增大CREATE\_BITMAP\_AREA\_SIZE初始化参数，可以加快查询处理  
  
  
　　该参数决定了分配给位图创建的内存大小，默认值为8MB。更大的值可以支持更大的连续位图，所以可以较快查询处理。  
  
  
　　- 增大BITMAP\_MERGE\_AREA\_SIZE初始化参数可以加快索引的范围扫描。该参数决定了索引范围扫描时合并位图分配的内存大小。默认值为1MB。  
  
  
　　7)位图索引示例  
  
  
　　------------------  
  
  
　　1个公司的客户数据  
  
  
　　CUSTOMER\#   MARITAL\_STATUS   REGION   GENDER  INCOME\_LEVEL  
  
  
　　---------   ---------------  -------- -------  ------------  
  
  
　　101        single           east     male    bracket\_1  
  
  
　　102        married          central  female  bracket\_4  
  
  
　　103        married          west     female  bracket\_2  
  
  
　　104        divorced         west     male    bracket\_4  
  
  
　　105        single           central  female  bracket\_2  
  
  
　　106        married          central  female  bracket\_3  
  
  
　　MARITAL\_STATUS，REGION，GENDER，INCOME\_LEVEL字段都是有较少的独特值(婚姻状况和地域只有三种值，性别只有2种值，收入级别只有4种值)，较为适合在这些字段上创建位图索引。但在CUSTOMER\#上不适合创建位图索引，因为该字段独特值较多。  
  
  
　　相反，在该字段上创建一个唯一的B\*tree索引，将十分有效。  
  
  
　　本例中，REGION字段上的位图索引。  
  
  
　　由三个分开的位图组成，每个region一个。  
  
  
　　REGION='east'  REGION='central'  REGION='west'  \#\#       CUSTOMER\#  
  
  
　　1              0                 0              <==       101  
  
  
　　0              1                 0              <==       102  
  
  
　　0              0                 1              <==       103  
  
  
　　0              0                 1              <==       104  
  
  
　　0              1                 0              <==       105  
  
  
　　0              1                 0              <==       106  
  
  
　　位图中的每个条目(或是说"bit")对应CUSTOMER表中的每一行。每个bit的值依赖于表中相应行的值。例如，REGION='east'的位图，它的第一个bit为1。这是因为CUSTOMER表的第一行，REGION='east'。REGION='east'的位图其他bit为0，是因为该表的其他行的REGION字段为其他值。  
  
  
　　一个分析员调查该公司客户的人口趋势，可能会问："居住在中心区或西区的客户有多少是已婚的？"，该问题就是下面这个查询SQL：  
  
  
　　SELECT COUNT(\*) FROM CUSTOMER  
  
  
　　WHERE MARITAL\_STATUS = 'married' AND REGION IN ('central','west');  
  
  
　　位图索引可以很高效地完成查询，因为只要计算位图中结果为1的数量，如图所示。最后要确认哪些客户符合要求，只要通过位图结果去访问表就可以了。  
  
  
　　status = 'married'     region = 'central'    region = 'west'  
  
  
　　0                          0                   0  
  
  
　　1                          1                   0  
  
  
　　1                          0                   1  
  
  
　　0                 AND(     0           OR      1       )  
  
  
　　0                          1                   0  
  
  
　　1                          1                   0  
  
  
　　0                           0              0  
  
  
　　1                           1              1    ==> 2nd row  
  
  
　　= 1                           1              1    ==> 3rd row  
  
  
　　0                 AND       1            = 0  
  
  
　　0                           1              0  
  
  
　　1                           1              1    ==> last row  
  
  
　　8)位图索引和NULLs  
  
  
　　---------------------  
  
  
　　位图索引包含的行有NULL值，和其他类型的索引完全不相同。对null值索引有利于一些类型的SQL语句，如查询包含了聚合函数COUNT。  
  
  
　　例1：  
  
  
　　-------  
  
  
　　SELECT COUNT(\*) FROM EMP;  
  
  
　　任何一个位图索引都可以用于该查询，因为表中所有行都被索引，包括那些NULL数据。如果null值未编入索引，优化器只有当，某个字段上有NOT NULL约束时，使用这个字段的索引。  
  
  
　　例2：  
  
  
　　-------  
  
  
　　SELECT COUNT(\*) FROM EMP WHERE COMM IS NULL;  
  
  
　　该查询可以通过在COMM上建立一个位图索引来优化。  
  
  
　　例3：  
  
  
　　-------  
  
  
　　SELECT COUNT(\*) FROM CUSTOMER WHERE GENDER = 'M' AND STATE != 'CA';  
  
  
　　该查询可以通过GENDER = 'M'的位图减去STATE = 'CA'的位图来得到结果。如果STATE字段包含NULL值(也就是说，该字段上没有NOT NULL约束)，那么STATE = 'NULL'的位图也必须从该结果中减去。  
  
  
　　9)表和视图  
  
  
　　--------------------  
  
  
　　通过TYPE='BITMAP'条件，可以在USER\_INDEXES，ALL\_INDEXES，DBA\_INDEXES视图中查询到位图索引的信息。  
  
  
　　10)执行计划上的标签  
  
  
　　---------------------  
  
  
　　参看：  
  
  
　　Oracle8 Tuning Release 8.0  
  
  
　　Oracle8i Tuning Release 8.1.5  
  
  
　　Oracle9i Database Performance Tuning Guide and Reference Release 1 (9.0.1)  
  
  
　　Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2)  
  
  
　　Oracle Database Performance Tuning Guide and Reference 10gRelease 1 (10.1)  
  
  
　　Oracle Database Performance Tuning Guide and Reference 10gRelease 2 (10.2)  
  
  
　　Chapter Using EXPLAIN PLAN  
  
  
　　11)使用位图索引的HINT(7.3 - 8.0 - 8.1 - 9.0 - 9.2 - 10.1 - 10.2)  
  
  
　　-------------------------  
  
  
　　INDEX  
  
  
　　INDEX\_COMBINE  
  
  
　　12)初始化参数  
  
  
　　-------------------------  
  
  
　　<Parameter:CREATE\_BITMAP\_AREA\_SIZE>  
  
  
　　<Parameter:BITMAP\_MERGE\_AREA\_SIZE>  
  
  
　　<Parameter:B\_TREE\_BITMAP\_PLANS>

<Parameter:V733\_PLANS\_ENABLED>

转载地址：http://edu.cnzz.cn/201007/649422ac.shtml

[Oracle]: http://blog.csdn.net/yxl626571494/article/details/50876298
[Oracle 1]: http://so.csdn.net/so/search/s.do?q=Oracle&t=blog
[Link 1]: http://so.csdn.net/so/search/s.do?q=%E4%BD%8D%E5%9B%BE%E7%B4%A2%E5%BC%95&t=blog
[category_icon.jpg]: /images/20220529/82597495165a4861804a989d2cc77267.png
[arrow_triangle_20_down.jpg]: /images/20220529/b99c4a49cb35451fb6525ece28fa12a3.png