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