刷新物化视图很慢_ORA-12052,不能建立快速刷新物化视图的解决

ORA-12052,不能建立快速刷新物化视图的解决

SQL> CREATE MATERIALIZED VIEW mv_vcard_last_hlrlist refresh fast as

select vcard.username,

vcard.nick_name,vcard.pcode,hlrlist.region,

vcard.birth_year, vcard.icon, vcard.descr, last.state,

vcard.HLR_CITY, vcard.BIRTH_MONTH, vcard.BIRTH_DAY

from hlrlist ,last ,vcard

where vcard.username = last.username and vcard.hlr_city = hlrlist.hlr_id

and vcard.status = 0 and last.state='Online' ;

2 3 4 5 6 7 8 and vcard.status = 0 and last.state='Online'

*

ERROR at line 8:

ORA-12052: cannot fast refresh materialized view IMPS.MV_VCARD_LAST_HLRLIST

begin

DBMS_MVIEW.EXPLAIN_MVIEW('select vcard.username,vcard.nick_name,vcard.pcode,hlrlist.region,vcard.birth_year, vcard.icon, vcard.descr, last.state,vcard.HLR_CITY, vcard.BIRTH_MONTH, vcard.BIRTH_DAY from hlrlist ,last ,vcard where vcard.username = last.username and vcard.hlr_city = hlrlist.hlr_id');

END;

SELECT CAPABILITY_NAME, POSSIBLE, MSGTXT

FROM MV_CAPABILITIES_TABLE

WHERE CAPABILITY_NAME LIKE 'REFRESH%';

CAPABILITY_NAME P

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

MSGTXT

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

REFRESH_COMPLETE Y

REFRESH_FAST N

REFRESH_FAST_AFTER_INSERT N

the SELECT list does not have the rowids of all the detail tables

CAPABILITY_NAME P

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

MSGTXT

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

REFRESH_FAST_AFTER_ONETAB_DML N

see the reason why REFRESH_FAST_AFTER_INSERT is disabled

REFRESH_FAST_AFTER_ANY_DML N

see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

REFRESH_FAST_PCT N

PCT is not possible on any of the detail tables in the materialized view

发现是因为没有包括各个表的rowid

于是改为:

CREATE MATERIALIZED VIEW mv_vcard_last_hlrlist refresh fast as

select hlrlist.rowid arowid,last.rowid browid,vcard.rowid crowid, vcard.username,

vcard.nick_name,vcard.pcode,hlrlist.region,

vcard.birth_year, vcard.icon, vcard.descr, last.state,

vcard.HLR_CITY, vcard.BIRTH_MONTH, vcard.BIRTH_DAY

from hlrlist ,last ,vcard

where vcard.username = last.username and vcard.hlr_city = hlrlist.hlr_id and last.state='Online' ;

成功建立该物化视图

分享到：

2008-04-21 21:26

浏览 717

评论