有关位图连接索引(bitmap join index)的一些测试

数据库版本:10.2.0.4

create table zsj_obj as select owner,object_id from dba_objects where object_id is not null;

create table zsj_obj_detail pctfree 90
as select object_id,object_name,object_type,last_ddl_time+rownum last_ddl_time,status
from dba_objects where object_id is not null;

alter table zsj_obj_detail add constraint pk_zsj_obj_detail primary key(object_id);

create bitmap index ind_zsj_obj_detail
on zsj_obj_detail(o.owner)
from zsj_obj o,zsj_obj_detail d
where o.object_id=d.object_id;

指向zsj_obj_detail的数据行.where子句中必须连接另一个表zsj_obj的主键或者是唯一键.如果zsj_obj是复合主键的话,where子句中必须包含zsj_obj的所有主键列.

create bitmap index ind_zsj_obj_detail
on zsj_obj_detail(o.owner)
from zsj_obj o,zsj_obj_detail d
where o.object_id=d.object_id
ORA-25954: missing primary key or unique constraint on dimension
这里报错了,因为zsj_obj的object_id不是主键也不是唯一键,虽然说zsj_obj_detail的object_id是主键吧,但那不是我这里要求的.

alter table zsj_obj_detail drop constraint pk_zsj_obj_detail;  我把zsj_obj_detail上的主键给去掉了
alter table zsj_obj add constraint pk_zsj_obj primary key(object_id);  加上了zsj_obj的主键约束
SQL> desc zsj_obj;
Name      Type         Nullable Default Comments
--------- ------------ -------- ------- --------
OWNER     VARCHAR2(30) Y                        
OBJECT_ID NUMBER                                 
SQL> desc zsj_obj_detail;
Name          Type          Nullable Default Comments
------------- ------------- -------- ------- --------
OBJECT_ID     NUMBER        Y                        
OBJECT_NAME   VARCHAR2(128) Y                        
OBJECT_TYPE   VARCHAR2(19)  Y                        
LAST_DDL_TIME DATE          Y                        
STATUS        VARCHAR2(7)   Y      

这样就可以创建上面的位图连接索引了:
create bitmap index ind_zsj_obj_detail
on zsj_obj_detail(o.owner)
from zsj_obj o,zsj_obj_detail d
where o.object_id=d.object_id;

Index created
索引创建成功了.

SQL> SELECT INDEX_TYPE,join_index,TABLE_NAME FROM USER_INDEXES WHERE INDEX_NAME=UPPER('ind_zsj_obj_detail');
INDEX_TYPE                  JOIN_INDEX TABLE_NAME
--------------------------- ---------- ------------------------------
BITMAP                      YES        ZSJ_OBJ_DETAIL
这里显示是zsj_obj_detail表上的索引.

exec dbms_stats.gather_table_stats(user,'ZSJ_OBJ',cascade=>true,estimate_percent=>100,method_opt=>'for columnS OWNER SIZE 254,OBJECT_ID SIZE 1');
exec dbms_stats.gather_table_stats(user,'ZSJ_OBJ_DETAIL',cascade=>true,estimate_percent=>100,method_opt=>'for all columnS size 1');
分析表zsj_obj_detail连带它的索引也分析的时候,ind_zsj_obj_detail也被分析了,可以知道这个索引确实是zsj_obj_detail这个表的索引.

SQL> SELECT OWNER,COUNT(1) CNT FROM ZSJ_OBJ GROUP BY OWNER ORDER BY CNT DESC;
OWNER                                 CNT
------------------------------ ----------
SYS                                 23328
......
TSMSYS                                  3
 
21 rows selected
 
SQL> select TABLE_NAME,COLUMN_NAME,NUM_BUCKETS,HISTOGRAM from User_Tab_Columns where table_name in('ZSJ_OBJ','ZSJ_OBJ_DETAIL');
TABLE_NAME                     COLUMN_NAME                    NUM_BUCKETS HISTOGRAM
------------------------------ ------------------------------ ----------- ---------------
ZSJ_OBJ                        OWNER                                   21 FREQUENCY
ZSJ_OBJ                        OBJECT_ID                                1 NONE
ZSJ_OBJ_DETAIL                 OBJECT_ID                                1 NONE
ZSJ_OBJ_DETAIL                 OBJECT_NAME                              1 NONE
ZSJ_OBJ_DETAIL                 OBJECT_TYPE                              1 NONE
ZSJ_OBJ_DETAIL                 LAST_DDL_TIME                            1 NONE
ZSJ_OBJ_DETAIL                 STATUS                                   1 NONE
 
7 rows selected

select COUNT(1)
from