| Issue with Materialized View after 10g Upgradation [message #403818] |
Mon, 18 May 2009 23:42  |
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 #403969 is a reply to message #403818] |
Tue, 19 May 2009 22:27  |
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;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
