本文标题:位图链接索引【本站如从外站转载,会注明出处】
本文链接:http://www.validba.net/2009/02/%e4%bd%8d%e5%9b%be%e9%93%be%e6%8e%a5%e7%b4%a2%e5%bc%95/
oracle中一个特殊索引:位图链接索引,位图家族的一类。它的创建思想是用另外某张表的列在指定的表上创建位图索引.有关位图索引和B*索引的区别可以查看oracle文档.在创建位图链接索引时,必须有一个先决条件:链接表有一个主键或者唯一键链接到指定创建索引的表
通过一个例子说明:
准备测试数据:
[oracle@neworacle oracle]$ sqlplus mayp/mayp
SQL*Plus: Release 10.1.0.5.0 – Production on Fri Jun 20 17:18:14 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Production
With the Partitioning and Data Mining options
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 – Production
With the Partitioning and Data Mining options
SQL>
SQL> create table test as select * from all_objects;
Table created.
SQL> create table test1 as select * from test where rownum<5000;
Table created.
SQL> alter table test add constraint test_pk primary key(OBJECT_ID);
Table altered.
SQL> alter table test1 add constraint test1_pk primary key(OBJECT_ID);
Table altered.
以上两张表中分别有一个主键.而且两张表中有一个相同的列object_id,可以用来关联.
现在想做的是查询test1表中object_name=’DBA_TABLES’的行在test表中有多少记录:
SQL> select count(*) from test1 a,test b where a.object_id=b.object_id and a.object_name=’DBA_TABLES’;
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=17 Card=1 Bytes=25
)
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=17 Card=1 Bytes=25
)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=17 Card=1 Bytes=25)
3 2 TABLE ACCESS (FULL) OF ‘TEST1′ (TABLE) (Cost=16 Card=1
Bytes=20)
2 1 NESTED LOOPS (Cost=17 Card=1 Bytes=25)
3 2 TABLE ACCESS (FULL) OF ‘TEST1′ (TABLE) (Cost=16 Card=1
Bytes=20)
4 2 INDEX (UNIQUE SCAN) OF ‘TEST_PK’ (INDEX (UNIQUE)) (Cos
t=0 Card=1 Bytes=5)
t=0 Card=1 Bytes=5)
以上的查询执行路径是从test1表中查找object_name=’DBA_TABLES’的行,然后用object_id去跟test表中的object_id关联,得出结果.我们现在创建一个位图链接索引:
根据上面的先决条件,test1表中必须有一个主键或唯一键可以跟test表关联.
SQL> create bitmap index test_on_test1_obname ON test(b.object_name) from test a,test1 b where a.object_id=b.object_id;
Index created.
以上语法的目的是：用test1表中的object_name列在test表创建一个位图索引,条件是from test a,test1 b where a.object_id=b.object_id
SQL> execute dbms_stats.gather_table_stats(ownname=>’MAYP’,tabname=>’TEST’,cascade=>true);
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*) from test1 a,test b where a.object_id=b.object_id and a.object_name=’DBA_TABLES’;
SQL> select count(*) from test1 a,test b where a.object_id=b.object_id and a.object_name=’DBA_TABLES’;
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT) (Cost=1 Card=4 Bytes=20)
3 2 BITMAP INDEX (SINGLE VALUE) OF ‘TEST_ON_TEST1_OBNAME’
(INDEX (BITMAP))
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT) (Cost=1 Card=4 Bytes=20)
3 2 BITMAP INDEX (SINGLE VALUE) OF ‘TEST_ON_TEST1_OBNAME’
(INDEX (BITMAP))
看看前后两个执行计划的区别:
- 计划二比计划一相比,只用到了’TEST_ON_TEST1_OBNAME’索引,所有结果都来自于这个索引;
- 没有回表查询
这个索引如果在一个庞当的数据仓库环境,肯定会发挥很大的优势;如果在一个高并发的OLTP环境,反而会影响性能.
任何的索引创建都必须符合当前的环境,毕竟位图索引还是比较"昂贵"的.
CopyRight Validba'S Home 版权所有.本站为个人学习之用,评论时请遵守本国法律
