bitmap join index

 

2010-03-25 8:06 SQL> conn gkl/gkl@orcl 已连接。 SQL> create bitmap index testmap on t1(t2.owner) from t1,t2 where t1.object_id=t 2.object_id; create bitmap index testmap on t1(t2.owner) from t1,t2 where t1.object_id=t2.obj ect_id * 第 1 行出现错误: ORA-25954: 维的主键或唯一约束条件缺失 #t2.object_id上没有主键或者唯一键 SQL> alter table t2 add constraint ind primary key (object_id); 表已更改。 SQL> create bitmap index testmap on t1(t2.owner) from t1,t2 where t1.object_id=t 2.object_id; 索引已创建。 SQL> explain plan for select * from t1,t2 where t1.object_id=t2.object_id and t2 .owner='GKL'; 已解释。 SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------Plan hash value: 1773484715 -------------------------------------------------------------------------------- --------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 249 | 2 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 249 | 2 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 177 | 1 (0)| 00:00:01 | | 3 | BITMAP CONVERSION TO ROWIDS| | | | || |* 4 | BITMAP INDEX SINGLE VALUE | TESTMAP | | | || |* 5 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 72 | 1 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | IND | 1 | | 0 (0)|0:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- --------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("T1"."SYS_NC00014$"='GKL') 5 - filter("T2"."OWNER"='GKL') 6 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 已选择20行。 下面注意建立BITMAP JOIN INDEX的限制 Join results must be stored, therefore, bitmap join indexes have the following restrictions: 1.Parallel DML is only supported on the fact table. Parallel DML on one of the participating dimension tables will mark the index as unusable. 2.Only one table can be updated concurrently by different transactions when using the bitmap join index. 3.No table can appear twice in the join. 4.You cannot create a bitmap join index on an index-organized table or a temporary table. 5.The columns in the index must all be columns of the dimension tables. 6.The dimension table join columns must be either primary key columns or have unique constraints. 7.The dimension table column(s) participating the join with the fact table must be either the primary key column(s) or with the unique constraint. 8.If a dimension table has composite primary key, each column in the primary key must be part of the join. 9.The restrictions for creating a regular bitmap index also apply to a bitmap join index. For example, you cannot create a bitmap index with the UNIQUE attribute. See Oracle Database SQL Reference for other restrictions.