Question

如何提高Oracle位图索引的使用效果

Answer 1

位图索引是Oralce数据库索引中的异类，其在某些比较特殊的场合中有突出的表现。一般来说，位图索引的效果直接跟列的基数相关。为此在谈到如何提高位图索引的使用效果时，也往往跟这个列的基数相关。为此必须对这个列的基数有一个清晰的认识。
一、什么时候改采用位图索引
基数是指某个列可能拥有的不重复数值的个数。比如说在一个中华人民共和国公民的信息表中，包含着十几亿条的记录。但是在这些记录中，有几个比较特殊的列，其指包含几个有限的重复数值。如性别这一字段，其就只有男与女两个可能值;如在民族一列内也只有56个不重复的值;如在出身地一列内，中国只有34个省市自治区。
有时候用户可能需要根据这些列来查询相关的数据。如公司搞活动的时候，可能需要确认一下活动有没有涉及到宗教的禁忌，如可能涉及到回族的禁忌的话，那么就需要查询一下员工信息系统，看看公司有没有回族的员工。如公司可能在三八妇女节的时候，会给女员工提供一定的福利，为此也需要在数据库中拉出女员工的名单，此时也就是根据性别这个字段来查询数据。等等。如果用户查询的列的基数非常的小，只是有限的几个固定值，如性别、民族、行政区等等。要为这些基数值比较小的列建立索引时，就需要建立位图索引。如果一定要说一个具体的标准的话，那么笔者认为如果基数值在整个表记录的2%以内或者字段内容的重复值在100次以上，则通过位图索引可以起到不错的效果。
大部分情况下都是通过基数值来确定是否需要使用位图索引。但是还有一种比较特殊的情况，可能这个列的基数值非常的大，也就是说这个列中的值重复性不是很高。但是只要起满足一定的条件，那么在这个字段上创建位图索引，也可以起到不错的效果。一般来说，如果字段往往在Where查询条件语句中被用到，并且采用的运算符为AND
或者OR 的逻辑运算符号的话，那么其效果也比建议其他索引要好的多。
二、把需要建立位图索引的列设置为固定长度的数据类型。
在Oracle数据库中，数据类型从大的来说，包括固定长度的数据类型与可变长度的数据类型。如就拿字符型数据来说，就有固定长度的字符串类型(char)与可变长度的字符串类型(VARCHAR2

)。这两个数据类型都是字符串数据类型，它们的差别主要在于字符串长度的控制上。CHAR存储固定长度的字符串。如果实际存储的字符串没有达到其规定的最大值的话，则数据库系统会自动在后面加上空格。而VARCHAR2则主要用来存储可变长度的字符数据类型。如存储固定长度的CHAR字符串类型不同，如果在这个列中存储的数据没有达到规定的最大值，
则数据库系统不会在最后加入空格，以实际的字符串存储。如果从数据库的体积上来看，肯定是可变长的数据类型比较好。
但是如果从位图索引的效果上来看，则最好把建立位图索引的列设置为固定长度的数据类型。因为位图索引使用固定长度的数据类型要比可变长度的数据类型在性能上要更加的优越。也就是说，如果要在某个字符类型的列上建立位图索引，那么最好把这个列的数据类型设置为char(即使其实际存储的长度不同)，而不是设置额外NVCHAR2。因为相对于性能的提升来说，这点空间的损失仍然是值得的。
另外我们也可以通过其他一些手段来保证列中存储内存长度的一致。如拿中国的行政区为例，大部分行政区就是3个字，如浙江省;但是长度比较长的有8个字符，如新疆维吾尔自治区。如果记录比较多的话，累积下来确实会造成比较大的浪费。在这种情况下，数据库管理在设计的时候，可以采用一些折中的处理方式。如利用简称来代表各个省份的名字。还有一种就是在存储的时候利用数字来表示省份，然后再在另外一张表中建立数字与省份之间的对应关系。笔者建议采用的是后面一种方式。
总之如果要为某个列建立位图索引的话，则数据库管理员最好能够想方设法让这个字段采用固定长度的数据类型，有时候即使牺牲一点存储空间也是在所不辞。
三、位图索引的使用限制。
虽然说位图索引在基数比较小的列或者使用到逻辑运算符时能够显著的提高数据库的查询性能，但是其在使用的时候仍然有不少的限制。数据库管理员需要了解这些限制，如此的话在数据库设计时才不会竹篮子打水一场空。
如把某个字段设置为位图索引的话，最好采用固定长度的数据类型。同时这个长度有最大的限制。在最新的Oracle数据库版本中，这个位图索引的最大长度不能够超过30。不知道以后的版本中会不会放宽这个长度上的限制。如位图索引不能够被声明为唯一索引;如位图索引不包含认为列数据，并且不能够用于任何类型的完整性检查;如当执行ALTER
TABLE语句并修改包含有位图索引的列时，这会使位图索引失效。

最重大的一个限制是基于规则的优化器不会考虑位图索引。Oracle数据库系统在执行一个SQL之前，首先要分析一下语句的执行计划，然后再按执行计划去执行。分析语句的执行计划的工作是由优化器来完成的。Oracle的优化器共有两种的优化方式，即基于规则的优化方式和基于成本的优化方式。基于规则的优化方式是指优化器在分析SQL语句时，所遵循的是Oracle内部预定的一些规则。基于成本的优化方式是看语句的执行成本。这里的成本主要指Cpu和内存。优化器在判断是否用这种方式时，主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。然后数据库系统会根据这些信息来确定是否需要采用这个优化方式。另外还有根据这两个优化模式衍生出来的优化器。
总之数据库管理理员需要明确的一点就是，如果要采用位图索引的话，则最好把数据库的优化器选择为基于成本的优化器模式。因为如果数据库采用的是基于规则的优化器模式的话，则会忽略位图索引。那么此时数据库管理员所建立的位图索引将一无用处。这一点大部分数据库管理员可能并不清楚，一定要引以为鉴。
四、同一个查询中合并多个位图索引。
通常情况下在同一个查询中合并多个位图索引后，可以使得数据库的查询性能得到显著的提高。也就是说，如在员工信息表中有性别、婚姻状态、职称等字段。这些字段都是基数比较低的字段，可以用来创建位图索引。如果现在用户在查询的时候，需要根据上面这三个字段来查询员工的信息，如需要查找性别为女、婚姻状态为已婚、职称为经理层以上的员工信息，为他们举行一个家庭派对的活动。此时在查询条件中就需要用到这个三个位图索引字段。数据库在执行查询计划的时候，如果Where字句中包含了这些位图索引字段的话，择优化器会依次使用3个单独的位图索引。每个位图记录指针，用于指示表中哪些行包含位图中的已知值。有了这些重要信息之后，数据库会执行一个位图AND操作并将从所有的四个位图中返回哪些行。然后再把这些值转换为ROWID值，并且查询继续完成剩余的处理工作(根据ROWID的值查询其他的信息)。
也就是说，如果在一个查询条件语句中，采用了多个位图索引来进行查询的话，其查询的效果是1+1〉2的效果。为此在应用程序设计中，可以把一些位图索引的字段作为查询条件都放置在查询窗口中，以明示的方式让用户选择查询条件。这对于提高应用程序的查询性能具有很大的帮助。
在Oracle数据库中一个表中最大可以支持30个位图索引。当然实际工作中往往用不了这么多。因为通常情况下只有在基数值比较小的列上利用这个位图索引才能够起到比较好的效果。相反如果在一些基数值比较大的列上采用位图索引的话，反而会起到相反的效果。而在一张表中基数列小的值不会很大。所以说数据库管理员更加需要关注这些基数列比较小的值。如果用户有对这些基数值小的列进行查询的习惯，那么数据库管理员就不能够有任何吝啬，要马上把这些列设置为位图索引。并且在查询设计的时候，最好能够在同一个查询中合并多个位图索引，以提高查询的性能。