湖北OLAP：ORA-12801，ORA-01410

湖北的OLAP系统，报表出错：
ORA-12801: 并行查询服务器 P002 中发出错误信号
ORA-01410: 无效的 ROWID
初步诊断是查询时候访问的index的rowid所指向的rowid找不到对应行
本着凡事先问metalink的原则，发现metalink给出了一些解答，感觉最靠谱的如下（id：734513.1）：
Cause
The issue can occur when a index block delete is not completed.

ROWID's are found in the index block leaf.

Solution

~. Run the explain plan on the table with the update statement to fnd the offending index.
~. Run the following to find the index statement:
set long 100000
select dbms_metadata.get_ddl('INDEX','','') from dual;
~. Drop the index

~. Recreate the index.

The update should run without error

NOTE: The index must be dropped and recreated. An online rebuild will not fix the bad ROWID

3). In essence, It appears that an Index re-build could well resolve this issue.

4). If not then, please take this issue up with Oracle Database Technical Support as this does not seem to be an issue with Informatica or with Oracle Business Intelligence.

需要删除查询锁用到的index，并重新创建，rebulid online还不行。
因为是olap，并发用户访问量少，所以就大刀阔斧的开搞：
1)得到执行计划，找出索引
explain plan for  select ...

select * from table(DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            |  Name                     | Rows  | Bytes |TempSpc| Cost  | Pstart| Pstop |  TQ    |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           |   100 | 31900 |       |   465K|       |       |        |      |            |
|*  1 |  VIEW                                |                           |   100 | 31900 |       |   465K|       |       |        |      |            |
|*  2 |   COUNT STOPKEY                      |                           |       |       |       |       |       |       |        |      |            |
|   3 |    VIEW                              |                           |  9571 |  2860K|       |   465K|       |       | 86,06  | P->S | QC (ORDER) |
|*  4 |     SORT ORDER BY STOPKEY            |                           |  9571 |  6000K|       |   465K|       |       | 86,06  | PCWP |            |
|*  5 |      SORT ORDER BY STOPKEY           |                           |   100 | 30600 |       |       |       |       | 86,05  | P->P | RANGE      |
|   6 |       SORT GROUP BY                  |                           |  9571 |  6000K|       |   465K|       |       | 86,05  | PCWP |            |
|   7 |        SORT GROUP BY                 |                           |  9571 |  6000K|       |   465K|       |       | 86,04  | P->P | HASH       |
|*  8 |         HASH JOIN                    |                           |  9571 |  6000K|       |   465K|       |       | 86,04  | PCWP |            |
|   9 |          NESTED LOOPS                |                           |     1 |   318 |       | 29166 |       |       | 86,03  | P->P | HASH       |
|  10 |           NESTED LOOPS               |                           |    36 | 10188 |       | 29164 |       |       | 86,03  | PCWP |            |
|  11 |            NESTED LOOPS              |                           |   718 |   189K|       | 29164 |       |       | 86,03  | PCWP |            |
|* 12 |             HASH JOIN                |                           | 71818 |    16M|       | 29164 |       |       | 86,03  | PCWP |            |
|* 13 |              TABLE ACCESS FULL       | STAT_DIM_AREA             |     1 |    22 |       |     2 |       |       | 86,00  | S->P | BROADCAST  |
|* 14 |              HASH JOIN               |                           |  7181K|  1547M|       | 29162 |       |       | 86,03  | PCWP |            |
|* 15 |               TABLE ACCESS FULL      | STAT_DIM_CITY             |     1 |    88 |       |     2 |       |       | 86,01  | S->P | BROADCAST  |
|  16 |               PARTITION LIST ALL     |                           |       |       |       |       |     1 |   131 | 86,03  | PCWP |            |
|  17 |                TABLE ACCESS FULL     | STAT_FACT_WATCHPART       |   718M|    92G|       | 29160 |     1 |   131 | 86,03  | PCWP |            |
|  18 |             INLIST ITERATOR          |                           |       |       |       |       |       |       | 86,03  | PCWP |            |
|* 19 |              INDEX UNIQUE SCAN       | PK_STAT_DIM_CPSP          |     1 |    22 |       |       |       |       | 86,03  | PCWP |            |
|* 20 |            INDEX UNIQUE SCAN         | PK_STAT_DIM_DATE          |     1 |    13 |       |       |       |       | 86,03  | PCWP |            |
|* 21 |           TABLE ACCESS BY INDEX ROWID| STAT_DIM_CONTENT_TYPE     |     1 |    35 |       |     1 |       |       | 86,03  | PCWP |            |
|* 22 |            INDEX UNIQUE SCAN         | PK_STAT_DIM_CONTENT_TYPE  |   100 |       |       |       |       |       | 86,03  | PCWP |            |
|  23 |          VIEW                        | STAT_DIM_CONTENT          |  2665K|   823M|       |   436K|       |       | 86,02  | S->P | HASH       |
|  24 |           SORT UNIQUE                |                           |  2665K|  1878M|  4152M|   436K|       |       |        |      |            |
|  25 |            UNION-ALL                 |                           |       |       |       |       |       |       |        |      |            |
|  26 |             TABLE ACCESS FULL        | STAT_TOTAL_VODPROG        |  1762K|  1258M|       |  2077 |       |       |        |      |            |
|  27 |             TABLE ACCESS FULL        | STAT_TOTAL_TVOD           |   887K|   613M|       |  1047 |       |       |        |      |            |
|  28 |             TABLE ACCESS FULL        | STAT_TOTAL_SUBJECT        | 10129 |  3857K|       |    14 |       |       |        |      |            |
|  29 |             TABLE ACCESS FULL        | STAT_TOTAL_CHANNEL        |  4901 |  1866K|       |     7 |       |       |        |      |            |
-------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_001"."ROWNUM_">0)
   2 - filter(ROWNUM<=100)
   4 - filter(ROWNUM<=100)
   5 - filter(ROWNUM<=100)
   8 - access("STAT_FACT_WATCHPART"."CONTENT_CODE"="STAT_DIM_CONTENT"."CONTENT_CODE")
  12 - access("STAT_FACT_WATCHPART"."AREA_CODE"="STAT_DIM_AREA"."AREA_CODE")
  13 - filter("STAT_DIM_AREA"."AREA_CODE"='1')
  14 - access("STAT_FACT_WATCHPART"."CITY_CODE"="STAT_DIM_CITY"."CITY_CODE")
  15 - filter("STAT_DIM_CITY"."CITY_NAME"='??oo')
  19 - access("STAT_DIM_CPSP"."CP_CODE"='HBDJ' OR "STAT_DIM_CPSP"."CP_CODE"='cp001')
       filter("STAT_FACT_WATCHPART"."CP_CODE"="STAT_DIM_CPSP"."CP_CODE")
  20 - access("STAT_FACT_WATCHPART"."DATE_ID"="STAT_DIM_DATE"."DATE_ID")
       filter("STAT_DIM_DATE"."DATE_ID">=20110701)
  21 - filter("STAT_DIM_CONTENT_TYPE"."CTYPE_NAME"='TV')
  22 - access("STAT_FACT_WATCHPART"."CONTENT_TYPE"="STAT_DIM_CONTENT_TYPE"."CONTENT_TYPE")

Note: cpu costing is off

ò?????56DD?￡

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY'
  
  
2）删除重建：
用到了三个：
select dbms_metadata.get_ddl('INDEX','PK_STAT_DIM_CPSP','ZXDBM_890') from dual;
select dbms_metadata.get_ddl('INDEX','PK_STAT_DIM_DATE','ZXDBM_890') from dual;
select dbms_metadata.get_ddl('INDEX','PK_STAT_DIM_CONTENT_TYPE','ZXDBM_890') from dual;

3）由于是主键，删除的时候报错ORA-02429

使用alter table STAT_DIM_CONTENT_TYPE drop PRIMARY KEY;删除主键

4）测试
和谐

总结：心型数据模型，维度表的记录在500行以内，个人认为使用索引是完全没有必要的.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25801738/viewspace-703075/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25801738/viewspace-703075/