Uderc programming article aggregator sites

Error While Creating Fast Refresh Materialized view.

Tags:
Answers: Have 6 answers
Advertisement
Table Scripts:
CREATE TABLE CONTRACT_MASTER
  CONTRACT_SEQ                    NUMBER(10)    NOT NULL,
  PDN                             CHAR(5)       NOT NULL,
  APPID                           NUMBER(10)    NOT NULL,
  CONTRACT_LOB_DESC               VARCHAR2(20)  NOT NULL,
  CUSTOMER_NAME                   VARCHAR2(57)  NOT NULL,
  CONTRACT_DT                     DATE          NOT NULL,
  CONTRACT_RECD_DT                DATE          NOT NULL,
  HELD_OFFERING_DT                DATE          NOT NULL,
  DRAFT_AMT                       NUMBER(15,2)  NOT NULL,
  STATUS_DESC                                   VARCHAR2(20)  NOT NULL,
  GIF_UPLOAD_TM                                            TIMESTAMP     NOT NULL
CREATE table CONTRACT_COMMENTS
  CONTRACT_COMMENTS_SEQ           NUMBER(10)     NOT NULL,
  APPID                           NUMBER(10)     NOT NULL,
  COMMENTS                        VARCHAR2(1000) NOT NULL,
  GIF_UPLOAD_TM                                            TIMESTAMP      NOT NULL
Constraints on tables
ALTER TABLE CONTRACT_MASTER ADD
  CONSTRAINT XPKCONTRACT_MASTER PRIMARY KEY (CONTRACT_SEQ) USING INDEX ;
ALTER TABLE CONTRACT_COMMENTS ADD
  CONSTRAINT XPKCONTRACT_COMMENTS PRIMARY KEY (CONTRACT_COMMENTS_SEQ) USING INDEX ;
alter table CONTRACT_MASTER add CONSTRAINT XUIAPPCONTRACT_MASTER UNIQUE (APPID) USING INDEX;
CREATE INDEX XUIAPPCONTRACT_COMMENTS ON
CONTRACT_COMMENTS(APPID) ;
Materialized View Creation:
CREATE MATERIALIZED VIEW LOG ON CONTRACT_MASTER WITH PRIMARY KEY,ROWID;
CREATE MATERIALIZED VIEW LOG ON CONTRACT_COMMENTS WITH PRIMARY KEY, ROWID;
CREATE MATERIALIZED VIEW MV_CONTRACT_COMMENTS_HELDOFFERING
REFRESH FAST
ENABLE QUERY REWRITE AS
SELECT APPID,COMMENTS FROM CONTRACT_COMMENTS  WHERE APPID IN (
SELECT APPID FROM CONTRACT_MASTER WHERE STATUS_DESC = 'Held Offering' )
Errors generated:
ERROR at line 4:
ORA-12015: cannot create a fast refresh materialized view from a complex query
_*Afer That I have changed the query but still it was not created like:*_
CREATE  MATERIALIZED VIEW MV_CONT_COMMNTS_HELDOFFERNG
REFRESH FAST
ENABLE QUERY REWRITE AS
SELECT CONTRACT_COMMENTS_SEQ,c.APPID,COMMENTS
FROM CONTRACT_COMMENTS c,CONTRACT_MASTER  m 
WHERE m.APPID = c.APPID and m.STATUS_DESC = 'Held Offering'
*even though error displayed:
SQL> CREATE  MATERIALIZED VIEW MV_CONT_COMMNTS_HELDOFFERNG*
   2  REFRESH FAST
   3  ENABLE QUERY REWRITE AS
   4  SELECT CONTRACT_COMMENTS_SEQ,c.APPID,COMMENTS
   5  FROM CONTRACT_COMMENTS c,CONTRACT_MASTER  m
   6  WHERE m.APPID = c.APPID and m.STATUS_DESC = 'Held Offering';
FROM CONTRACT_COMMENTS c,CONTRACT_MASTER  m
ERROR at line 5:
ORA-12052: cannot fast refresh materialized view GSSIO.MV_CONT_COMMNTS_HELDOFFERNG
*Again I have done "Analyzing Materialized Views for Fast Refresh" as follows:*
1: exec dbms_mview.explain_mview('MV_CONT_COMMNTS_HELDOFFERNG');
2: SELECT capability_name,  possible, SUBSTR(msgtxt,1,60) AS msgtxt
           FROM mv_capabilities_table
           WHERE capability_name like '%FAST%';
Output is :
CAPABILITY_NAME                               P              MSGTXT                                                            
REFRESH_FAST                                   N                                                                   
REFRESH_FAST_AFTER_INSERT            N  the SELECT list does not have the rowids of all the detail t      
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 mater      
Please suggest what to do to implement fast refresh materialized view for same.Edited by: dba on Sep 20, 2010 12:00 AM
Advertisement
The best answer: If the two MVs have to be consistent with each other as of a specific time, put them into a Refresh Group and refresh them with DBMS_MVIEW.REFRESH
If an MV is dependent on another, use DBMS_MVIEW.REFRESH_DEPENDENT
See the "Oracle® Database PL/SQL Packages and Types Reference" documentation pages for DBMS_MVIEW.
Hemant K Chitale
http://hemantoracledba.blogspot.com --- this is NOT a documentation site.
Edited by: Hemant K Chitale on Sep 20, 2010 5:19 PM