1、首先创建两个测试表:create table FUT_SYGL_WTLJ (c1 int unique, c2 char(10), c3 date);
create table FUT_SYGL_WTLJ02(t1 int unique, t2 char(10), t3 date);
2、插入模拟数据,可以参考sql:
insert into FUT_SYGL_WTLJ values(1, 'a','2012-01-02');
insert into FUT_SYGL_WTLJ values(2, 'b','2012-02-02');
insert into FUT_SYGL_WTLJ values(3, 'c','2012-03-02');
insert into FUT_SYGL_WTLJ values(1, 'a','2012-01-02');
insert into FUT_SYGL_WTLJ values(2, 'b','2012-02-02');
insert into FUT_SYGL_WTLJ values(3, 'c','2012-03-02');
3、创建位图连接索引,执行如下SQL命令:
create or replace bitmap index FUT_SYGL_WTLJ_IDX on FUT_SYGL_WTLJ(c1) from FUT_SYGL_WTLJ, FUT_SYGL_WTLJ02 where FUT_SYGL_WTLJ.c1=FUT_SYGL_WTLJ02.t1;
4、查询操作,执行如下SQL命令:select * from FUT_SYGL_WTLJ, FUT_SYGL_WTLJ02 where FUT_SYGL_WTLJ.c1=FUT_SYGL_WTLJ02.t1 and FUT_SYGL_WTLJ02.t3='2012-01-02';
5、此时位图连接索引已经创建成功,此时可以对其更改、删除索引,重建索引,设置索引失效等可以参考sql:
ALTER INDEX FUT_SYGL_WTLJ_IDX RENAME TO FUT_SYGL_WTLJ_IDX02;
Drop index FUT_SYGL_WTLJ_IDX;
ALTER INDEX FUT_SYGL_WTLJ_IDX REBUILD;
ALTER INDEX FUT_SYGL_WTLJ_IDX UNUSABLE;