ORACLE位图索引
内容简介:
1.位图索引
1.1位图索引使用注意事项;
1.2 使用位图索引;
1.3 位图索引对DML操作的影响;
2.位图连接索引
2.1 明确需求后使用位图索引;
2.1创建位图连接索引的注意事项:
1.位图索引:
1.1位图索引使用注意事项:
❏ 一般适用于低基数列;
❏ 适合数据仓库;
❏ 对于启用位图索引的表,应尽量减少或避免DML操作;
❏ 如果对一张含有多列位图索引的表进行大量DML操作,应考虑将位图索引删除,DML操作结束后重建位图索引;
❏ 不适用于频繁持续发生DML操作的OLTP系统,会出现行锁定,阻碍更新性能;
1.2 使用位图索引
位图索引与B-TREE索引有很大的不同,一个位图索引由多个位串组成,每个位串都表示基础列中一个独立的有效值;每个位串是打开或关闭,表示该值是否用于某一行;以人员信息表th03 为例,性别(gender)字段的值(男、女、未记录),假如为其创建位图索引,那么每个位串(男、女、未记录)中的单个位表示一个给定行的值是男、女还是未记录;当判断某一列是否适合创建位图索引时,需要考虑是否符合”低基数列”,根据应用程序、数据组成以及数据库中的表的情况不同,是否创建位图索引的结论也可能不同;通常用来判断的一条基本经验法则是:”如果该列的有效值数目不足表中行数的1%,那么它就适合创建位图索引,以th03来说,表行数为:500万行,而性别列的有效值数目仅为3个( 男、女、未记录),可以确定它适合创建位图索引:
人员信息表(th03)
|
行 |
ID |
NAME |
GENDER |
IDCARD |
HOMEADDR |
JOBNO |
BIRTHDATE |
|
1 |
998698 |
李天 |
男 |
440623197007253619 |
水晶洞1 |
526456 |
25-JUL-70 |
|
2 |
998699 |
李花 |
女 |
510802197007251223 |
水晶洞2 |
5785452 |
25-JUL-70 |
|
3 |
584625 |
李某 |
未记录 |
564551545265642155 |
水晶洞3 |
1565452 |
01-JUL-88 |
SQL> create bitmap index ind_th03_gender on th03(gender) tablespace tbs03;
Index created.
Elapsed: 00:00:01.05
SQL> execute dbms_stats.gather_table_stats('sywu','th03',cascade=>true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.31
SQL> @/oracle/getind
TABLE_NAME INDEX_NAME COLUMN_NAME SIZE_GB INDEX_TY STATUS NUM_ROWS DISTINCT_KEYS
------------------------------ ------------------------------ ------------------------------ ---------- -------- -------- --------- --------
TH03 IND_TH03_GENDER GENDER .001953125 BITMAP VALID 453 3
位图索引的创建非常快并且占用的空间也非常小;位图索引和B-TREE索引存储值的方式不同,它存储表中的每一行值(包括空值),对于B-TREE索引,单列索引不存储空值,复合索引只要有一个非空值就可以存储;所以当执行 IS NULL 或者 IS NOT NULL 查询时位图索引的效率要高于B-TREE索引:
SQL> select count(*) from th03 where gender is null;
Elapsed: 00:00:00.03
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | BITMAP CONVERSION COUNT | | 1 | 5 | 1 (0)| 00:00:01 |
|* 3 | BITMAP INDEX SINGLE VALUE| IND_TH03_GENDER | | | | |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("GENDER" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
1 physical reads
如果在相同表相同列建立B-TREE索引,则该执行必须全表扫描:
SQL> select count(*) from th03 where gender is null;
Elapsed: 00:00:00.16
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 14908 (1)| 00:02:59 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| TH03 | 1 | 5 | 14908 (1)| 00:02:59 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("GENDER" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
54574 consistent gets
54562 physical reads
位图索引还可以在另外一些情况使用,如使用聚合函数:
SQL> select count(*),count(gender) from th03;
COUNT(*) COUNT(GENDER)
---------- -------------
5000000 5000000
Elapsed: 00:00:00.11
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 203 (0)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | BITMAP CONVERSION TO ROWIDS | | 5000K| 23M| 203 (0)| 00:00:03 |
| 3 | BITMAP INDEX FAST FULL SCAN| IND_TH03_GENDER | | | | |
-------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
232 consistent gets
0 physical reads
如果你决定在应用程序中实现位图索引,应时不时的检查建立了位图索引的列的数据组成,这一点很重要,如果你算错了包含位图索引的任何列的基数,可能会对应用程序造成负面影响,如(位图索引的存储空间会增加、查询性能会降低、重建索引的时间将增加);
1.3 位图索引对DML操作的影响:
为了便于测试,我创建另一张分区表 th04,并为th04的性别、出生日期列创建位图索引,最后向它插入100万行数据:
SQL> create table th04
partition by range(id)(
partition th04_part1 values less than(1000000) tablespace tbs03,
partition th04_part2 values less than(2000000) tablespace tbs03,
partition th04_part3 values less than(3000000) tablespace tbs03,
partition th04_part4 values less than(4000000) tablespace tbs03,
partition th04_part5 values less than(5000000) tablespace tbs03,
partition th04_part6 values less than(maxvalue) tablespace tbs03
) as select * from tbbase where 1=0 ;
Table created.
Elapsed: 00:00:00.04
SQL> create bitmap index ind_th04_gender on th04(gender) local tablespace tbs03;
Index created.
Elapsed: 00:00:00.04
SQL> create bitmap index ind_th04_birthdate on th04(birthdate) local tablespace tbs03;
Index created.
Elapsed: 00:00:00.09
SQL>insert into th04 select * from tbbase where rownum<1000001;
1000000 rows created.
Elapsed: 00:00:40.24
从结果可以看出,插入100万行的数据花费40秒,从表面上看花费的时间似乎是合理的,但当数据量不断增加时,特别对于一个数据仓库环境,一天处理几百万甚至亿行数据是司空见惯的,合理的情况应考虑删除位图索引(如果是分区表则将目标分区标记为不可用),执行完DML加载操作后重建位图索引;
SQL> alter index IND_TH04_GENDER unusable;
Index altered.
Elapsed: 00:00:00.18
SQL> alter index IND_TH04_BIRTHDATE unusable;
Index altered.
Elapsed: 00:00:00.41
SQL> insert into th04 select * from tbbase where rownum<1000001;
1000000 rows created.
Elapsed: 00:00:23.74
禁用位图索引后,插入100万行数据只花费23 秒,对于大数据而言这或许可以提高装载数据的性能;数据装载结束后重建位图索引:
SQL> alter index ind_th04_gender rebuild partition TH04_PART1;
alter index ind_th04_gender rebuild partition TH04_PART2;
alter index ind_th04_gender rebuild partition TH04_PART3;
alter index ind_th04_gender rebuild partition TH04_PART4;
alter index ind_th04_gender rebuild partition TH04_PART5;
alter index ind_th04_gender rebuild partition TH04_PART6;
alter index ind_th04_birthdate rebuild partition th04_part1;
.........
重建分区索引也可以通过下面的命令重建:
SQL> alter table th04 modify partition th04_part1 rebuild unusable local indexes;
Table altered.
Elapsed: 00:00:05.81
此命令虽然简单,但它也有不足之处,对于一个指定的分区,它只能按顺序执行;而对于每一个指令发出的命令重建分区,它可以同时执行多个语句,实现并行重建索引;
除装载数据的影响外,位图索引也会影响数据的DML操作,请观察下面的人员信息表数据:
人员信息表(th04)
|
行 |
ID |
NAME |
GENDER |
IDCARD |
HOMEADDR |
JOBNO |
BIRTHDATE |
|
1 |
998698 |
李天 |
男 |
440623197007253619 |
水晶洞1 |
526456 |
25-JUL-70 |
|
2 |
998699 |
李四 |
男 |
510802197007251223 |
水晶洞2 |
5785452 |
25-JUL-70 |
|
..... |
.... |
|
|
|
|
|
|
数据显示他们的出生日期是相同的,并且出生日期列(BIRTHDATE)还建立了位图索引( IND_TH04_BIRTHDATE) ,因业务错误记录两人的出生日期,so,现在对其修改:
---session 1-----
SQL> select distinct sid from v$mystat;
SID
----------
191
SQL> update th04 set birthdate='26-JUL-70' where idcard='440623197007253619';
1 row updated.
Elapsed: 00:00:00.11
此时session 1 的用户因为业务繁忙没有及时提交,这时另一个业务员在新的会话 session2中修该另一个错误记录:
----session 2 -----
SQL> select distinct sid from v$mystat;
SID
----------
194
SQL> update th04 set birthdate='27-JUL-70' where idcard='510802197007251223';
session 2 中的用户会一直处于等待状态,因为他们修改的错误人员出生日期在更新之前在同一个位图索引位串中,当修改位串中的位信息时位串会被锁定,直到更新提交后更新位串中的位值;观察此时的锁状态:
SQL>select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in(191,194) order by sid
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
191 AE 100 0 4 0 2052 0
191 TM 75667 0 3 0 263 0
191 TX 65562 848 6 0 263 1
191 TM 75668 0 3 0 263 0
191 TO 65927 1 3 0 286 0
194 TM 75668 0 3 0 238 0
194 TX 131081 1071 6 0 238 0
194 TM 75667 0 3 0 238 0
194 TX 65562 848 0 4 238 0
194 AE 100 0 4 0 1406 0
对于session 1(191)此时持有一个6级事务锁,并且堵塞session 2(194),它们请求的资源是一样的,这并非巧合;只有当session 1(191)提交或回退后,这个6级事物锁才会被释放,session 2(194)才能持有锁修改数据;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
191 AE 100 0 4 0 2832 0
191 TO 65927 1 3 0 1066 0
194 TX 131081 1071 6 0 1018 0
194 TM 75667 0 3 0 1018 0
194 TM 75668 0 3 0 1018 0
194 AE 100 0 4 0 2186 0
所以建立位图索引时,应仔细分析表结构和表数据,作出明智、合理选择;以上测试因环境、版本、数据库状态测试结果可能不同;
2.位图连接索引
2.1 明确需求后使用位图索引
在创建位图连接索引时,它是两个表或多个表之间的索引值的连接,连接的结果存储在索引自身中;通过前期做连接并存储结果,当查询时通过扫描索引(避免两表或多表全表扫描)来获取数据,当然对于建立这样的索引,建立前需求必须明确; 请观察如下人员信息表(th04)和人员单位关系信息表(tbbsj)
人员信息表(th04)
|
行 |
ID |
NAME |
GENDER |
IDCARD |
HOMEADDR |
JOBNO |
BIRTHDATE |
|
1 |
789524 |
张三 |
男 |
429005198911261805 |
水晶洞1 |
1300440 |
05-AUG-79 |
|
2 |
564895 |
李四 |
男 |
429005198911296178 |
水晶洞2 |
1000209 |
14-AUG-79 |
|
..... |
.... |
|
|
|
|
|
|
人员单位关系信息表(tbbsj)
|
行 |
ID(单位ID) |
IDCARD(人员身份证) |
DNAME(单位名称) |
JOINDATE(进入单位时间) |
SRZW(所任职位) |
GZZT(工作状态) |
TSGX(特殊贡献) |
|
1 |
785652 |
429005198911261805 |
太空建筑有限公司 |
2013-12-22 21:51:33 |
总经理助理 |
优 |
暂无 |
|
2 |
5689556 |
429005198911296178 |
飞轮实业有限公司 |
2013-12-22 21:51:42 |
总经理小秘 |
良 |
暂无 |
|
.. |
..... |
|
|
|
|
|
|
已知人员信息数据量为:100万,人员单位关系信息数据量为:1106642,在建立普通索引的情况下获取人员IDCARD为:429005198911261805 的人员单位信息:
SQL> select t1.*,t2.* from th04 t1,tbbsj t2
2 where t1.idcard=t2.idcard and t1.idcard='429005198911261805';
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 253 | 10959 (1)| 00:02:12 | | |
|* 1 | HASH JOIN | | 1 | 253 | 10959 (1)| 00:02:12 | | |
| 2 | PARTITION RANGE ALL| | 1 | 65 | 2661 (1)| 00:00:32 | 1 | 6 |
|* 3 | TABLE ACCESS FULL | TH04 | 1 | 65 | 2661 (1)| 00:00:32 | 1 | 6 |
|* 4 | TABLE ACCESS FULL | TBBSJ | 1 | 188 | 8297 (1)| 00:01:40 | | |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."IDCARD"="T2"."IDCARD")
3 - filter("T1"."IDCARD"='429005198911261805')
4 - filter("T2"."IDCARD"='429005198911261805')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
40165 consistent gets
40137 physical reads
分析结果得知为获取IDCARD为: 429005198911261805的人员单位信息,两张大表都做了全表扫描;因为获得人员单位关系信息需求已经明确,为其创建位图连接索引:
SQL> create bitmap index ind_th04uniontbbsj on th04(t1.idcard)
2 from th04 t1,tbbsj t2
3 where t1.idcard=t2.idcard
4 tablespace tbs03
5* local
SQL> /
Index created.
Elapsed: 00:00:01.65
创建位图连接索引后再次查询:
SQL> select t1.*,t2.* from th04 t1,tbbsj t2
2* where t1.idcard=t2.idcard and t1.idcard='429005198911261805';
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 253 | 5 (0)| 00:00:01 | | |
| 1 | NESTED LOOPS | | 1 | 253 | 5 (0)| 00:00:01 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | TBBSJ | 1 | 188 | 3 (0)| 00:00:01 | | |
|* 3 | INDEX UNIQUE SCAN | PK_IDCARD | 1 | | 2 (0)| 00:00:01 | | |
| 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| TH04 | 1 | 65 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 5 | INDEX UNIQUE SCAN | CS_IDCARD | 1 | | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."IDCARD"='429005198911261805')
5 - access("T1"."IDCARD"='429005198911261805')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
对于位图连接索引,它将主导表和关联表中的ROWID以及主导列的列值存储在索引中,查询数据时通过扫描索引提高查询效率:
|
人员信息表(TH04)ROWID |
人员单位关系信息表(tbbsj)ROWID |
IDCARD |
|
AAASdEAAGAAB6w6AAY |
AAASfXAAIAAAmCQAAE |
440621197102274116 |
|
AAASdEAAGAAB6z5AAf |
AAASfXAAIAAAmCGAAX |
440621197311244423 |
|
AAASdEAAGAAB6zCAAl |
AAASfXAAIAAAmCPAAW |
440621197405202427 |
2.2创建位图连接索引的注意事项:
2.2.1创建位图连接索引时WHERE 子句中的关联条件列必须是主键或唯一约束(不符合条件会报错ORA-25954: missing primary key or unique constraint on dimension);
2.2.2在指定索引列时,如果两表都具有相同列,必须使用表名.列名的方式指定或者别名.列名的方式指定(不符合条件会报错:ORA-00918: column ambiguously defined);
2.2.3 创建位图索引同样DML操作效率差,建立前请仔细分析表结构和数据DML操作率;
2.2.4 更新表数据时同样会有锁定主导表、关联表问题;
