限时优惠:Oracle认证免费重考优惠         OCM周末班 正在报名 ,欢迎垂询!         MySQL认证周末班 正在招生 欢迎垂询!         OCP 2月周末班,正在授课 欢迎试听         RHCE 3月周末班 正在招生 欢迎垂询         2019和思庄一起再创辉煌         

创建 bitmap join index报:ORA-25954

更新时间: 2018-09-17 13:41

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')

开班信息MORE>>

课程名称 开课时间 上课类型 状态
OCM认证 3月 周末班 报名中...
RHCE认证 2月 周末班 授课中...
OCP认证 2月 周末班 授课中...
RHCE认证 3月 周末班 报名中...
OCP认证 3月 周末班 报名中...
MySQL认证培训 常年 周末班 报名中...
RAC实用技术 常年 周末、脱产 报名中...
<<