1.问题现象：

    在创建位图连接索引时，报以下错误：

SQL> CREATE BITMAP INDEX bjx ON sales(c.cust_city,c.cust_state_province) 

  2  FROM sales s, customers c 

  3  WHERE s.cust_id = c.cust_id;

FROM sales s, customers c

              *

ERROR at line 2:

ORA-25954: missing primary key or unique constraint on dimension

 

2.错误原因

   检查错误帮助 ：   ORA-25954: missing primary key or unique constraint on dimension Cause: An attempt to create a join index was made, which failed because one or more dimensions did not have an appropriate constraint matching the join conditions.

Action: Ensure that the where clause is correct (contains all of the constraint columns) and that an enforced constraint is on each dimension table.

   提示原因是 维度表上没有建立主键或唯一键。

 

 

3.问题分析

  SQL> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS,DEFERRABLE,DEFERRED,VALIDATED from user_constraints where table_name='CUSTOMERS' and constraint_type in ('P','U');

OWNER                          CONSTRAINT_NAME                C STATUS   DEFERRABLE     DEFERRED  VALIDATED

------------------------------ ------------------------------ - -------- -------------- --------- -------------

SH                             CUSTOMERS_PK                   P ENABLED  NOT DEFERRABLE IMMEDIATE NOT VALIDATED

 

  检查发现，这个customer表的主键是存在的，而且是可用的，但validated为   NOT VALIDATED

  

  分析是否可以变为 validated

  SQL>  select count(*),count(distinct cust_id) from customers;

  COUNT(*) COUNT(DISTINCTCUST_ID)

---------- ----------------------

     55500                  55500

 

 

  数据都唯一 

 

 

4.问题解决

  启用validate

  SQL> alter table customers  enable validate constraint CUSTOMERS_PK  ;

Table altered.

  

  SQL>  select OWNER,CONSTRAINT_NAME,STATUS,DEFERRABLE,DEFERRED,VALIDATED from user_constraints where table_name='CUSTOMERS' and constraint_type in ('P','U');

OWNER                          CONSTRAINT_NAME                STATUS   DEFERRABLE     DEFERRED  VALIDATED

------------------------------ ------------------------------ -------- -------------- --------- -------------

SH                             CUSTOMERS_PK                   ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED

 

  再次去创建 Bitmap Join Index ,  问题解决。

  SQL> CREATE BITMAP INDEX bjx ON sales(c.cust_city,c.cust_state_province) 

  2  FROM sales s, customers c 

  3  WHERE s.cust_id = c.cust_id local;

Index created.

 

 

验证一下位图连接索引的使用 ：

 

SQL> SELECT sum(s.amount_sold)

  2  FROM sales s, customers c

  3  WHERE s.cust_id = c.cust_id 

  4  AND c.cust_city = 'ORLANDO' 

  5  AND c.cust_state_province = 'FL';

 

SQL> select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  7pucvv2053d4t, child number 0

-------------------------------------

SELECT sum(s.amount_sold) FROM sales s, customers c WHERE s.cust_id =

c.cust_id AND c.cust_city = 'ORLANDO' AND c.cust_state_province = 'FL'

 

Plan hash value: 187432387

 

-------------------------------------------------------------------------------------------------------------

| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

-------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |       |       |       |   260 (100)|          |       |       |

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|   1 |  SORT AGGREGATE                     |       |     1 |    10 |            |          |       |       |

|   2 |   PARTITION RANGE ALL               |       |  1708 | 17080 |   260   (0)| 00:00:04 |     1 |    28 |

|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES |  1708 | 17080 |   260   (0)| 00:00:04 |     1 |    28 |

|   4 |     BITMAP CONVERSION TO ROWIDS     |       |       |       |            |          |       |       |

|*  5 |      BITMAP INDEX SINGLE VALUE      | BJX   |       |       |            |          |     1 |    28 |

-------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   5 - access("S"."SYS_NC00009$"='ORLANDO' AND "S"."SYS_NC00010$"='FL')