Home » Server Options » Replication » Issue with Materialized View after 10g Upgradation (Oracle 10.2.0.4.0, SunOS5.9)
Issue with Materialized View after 10g Upgradation [message #403818] Mon, 18 May 2009 23:42 Go to next message
mmtr
Messages: 4
Registered: April 2009
Location: DELHI
Junior Member
Hi All,
I have a Materialized View that gets refreshed everyday.
While the DB running on oracle 9.2.0.7,It was taking around 50mins to refresh.I was using the following codes

dbms_mview.refresh('mvname','c');

After 10g upgradation the refresh process ran for more than 2 hr.
So I used the following codes

dbms_mview.refresh('mvname','atomic_refresh=>false')

It took 50mins to refresh. But what I found that the number of record count is same after each day refresh.Is there any problem with the option atomic_refresh=>false.
Why I used it -In Oracle 9.2.7.0 the 'c' option truncate the MVIEW then insert.But in Oracle 10.2.0.4.0 the 'c' option deletes the records and then insert .The option atomic_refresh=>false truncates.

Please suggest about this
Re: Issue with Materialized View after 10g Upgradation [message #403923 is a reply to message #403818] Tue, 19 May 2009 09:44 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Post your materialized view statement.

Babu
Re: Issue with Materialized View after 10g Upgradation [message #403969 is a reply to message #403818] Tue, 19 May 2009 22:27 Go to previous messageGo to next message
mmtr
Messages: 4
Registered: April 2009
Location: DELHI
Junior Member
CREATE MATERIALIZED VIEW SNAME.MV002AR_CUSTDUR_24MON
TABLESPACE TBSP_LARGE_DATA01
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE
START WITH TO_DATE('01-Jun-2009','dd-mon-yyyy')
NEXT add_months(trunc(sysdate, 'mm'),1)
WITH PRIMARY KEY
AS
SELECT a.load_prd, b.site_duns_no, c_cust_no ar_cust_no,
DECODE (SUBSTR (a.c_cust_no, 1, 2),
'95', (SELECT xcmf_017_orig_cust_no
FROM SNAME.t210xeep
WHERE xcmf_001_xeep_cust_no = a.c_cust_no),
a.c_cust_no
) AS c_cust_no,
a.open_invc_cnt
FROM SNAME.t231mckb_xref b, SNAMEE.ar_customer@DBLINK a
WHERE a.load_prd >= TO_CHAR ((ADD_MONTHS (SYSDATE, -24)), 'yyyymm')
AND a.open_invc_cnt > 0
AND b.cust_no =
DECODE (SUBSTR (a.c_cust_no, 1, 2),
'95', (SELECT xcmf_017_orig_cust_no
FROM SNAME.t210xeep
WHERE xcmf_001_xeep_cust_no = a.c_cust_no),
a.c_cust_no
);

CREATE INDEX SNAME.CII_MV002AR_CUSTDUR ON SNAME.MV002AR_CUSTDUR_24MON
(LOAD_PRD, SITE_DUNS_NO, AR_CUST_NO)
LOGGING
TABLESPACE TBSP_LARGE_DATA01
NOPARALLEL;

CREATE UNIQUE INDEX VCRADMIN.CI_MV002AR_CUSTDUR ON SNAME.MV002AR_CUSTDUR_24MON
(LOAD_PRD, SITE_DUNS_NO, AR_CUST_NO, C_CUST_NO)
LOGGING
TABLESPACE TBSP_LARGE_DATA01
NOPARALLEL;
Re: Issue with Materialized View after 10g Upgradation [message #404152 is a reply to message #403969] Wed, 20 May 2009 11:13 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

I think; it's Complex Materialized View Statement.

ASAIK; In oracle 10g; Complete materialized view takes more time to compare 9i; I already raise SR to oracle (6 months before); Still oracle working on this.

Let me check update & I'll get back to you. Don't close this thread.

Babu
Previous Topic: Alternate Solution for Materialized views
Next Topic: is it possible ?
Goto Forum:
  


Current Time: Thu Mar 28 10:08:21 CDT 2024