Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

i create a materialized view fast refresh. it takes almost 45 min to create but it did not refresh in 24 hours. i tried it on with index and without index. i check the log of all table max record in log table is 2 lac. query is as under please suggest what changes needed 

CREATE MATERIALIZED VIEW LOG ON a WITH ROWID,   SEQUENCE (COLUMN USED FROM THIS TABLE)
/
CREATE MATERIALIZED VIEW LOG ON P WITH ROWID,   SEQUENCE (COLUMN USED FROM THIS TABLE)
/
CREATE MATERIALIZED VIEW LOG ON PG WITH ROWID,  SEQUENCE (COLUMN USED FROM THIS TABLE)
/
CREATE MATERIALIZED VIEW LOG ON PN WITH ROWID, SEQUENCE (COLUMN USED FROM THIS TABLE))
/ 
CREATE MATERIALIZED VIEW LOG ON AP WITH ROWID
/
CREATE  MATERIALIZED VIEW C_INFO 
NOLOGGING
BUILD IMMEDIATE
refresh fast with rowid 
on demand
AS
SELECT  
   A.ROWID ACTROWID , P.ROWID PREMROWID, 
   PG.ROWID PGROWID,AP.ROWID APROWID, PN.ROWID PNROWID,
   ...
FROM     A,    P,   pg,    ap, pn      
WHERE   
        p.id             =  pg.id (+)
    and pg.columname  (+)=  'Value' 
...
share|improve this question
First, check and see if the refresh is actually fast: SELECT mview_name, refresh_mode, refresh_method, last_refresh_type, last_refresh_date FROM user_mviews; There are so many things that could be going wrong here. Have you asked your DBA to do a full session trace when executing a refresh? What data volumes are involved here on the 5 tables involved? – Phil Apr 12 '12 at 8:50
dear Phil thanks for response i already checked from user_mviews it is fast refresh. there are about 5 million plus records in view and 2 lack in view log. – hafiz zahid Apr 12 '12 at 9:30
2 lakh = 200000 ? – Phil Apr 12 '12 at 9:37
Thanks yes 2 lakh = 200000 – hafiz zahid Apr 12 '12 at 9:41

1 Answer

up vote 3 down vote

Your materialized is not defined with a NEXT clause, therefore it will only refresh when you ask for it explicitely. You can use either DBMS_MVIEW.REFRESH directly or create a refresh group with DBMS_REFRESH.

In order to automate the refresh, you could program a job with DBMS_SCHEDULER or DBMS_JOB (dbms_job is deprecated in 11g).

You could also define your MV with a NEXT clause, for example this will refresh the MV every hour:

CREATE MATERIALIZED VIEW  C_INFO 
NOLOGGING
BUILD IMMEDIATE
refresh fast with rowid 
on demand
NEXT sysdate + 1/24
share|improve this answer
Ah, I'd read the question as "hadn't finished refreshing in 24 hours" rather than "hadn't started refreshing" :) – Phil Apr 12 '12 at 9:15
i am not sure if it is sufficient to specify NEXT. I read the manual an I think it is also necessary to specify STARTING WITH. – miracle173 Apr 12 '12 at 15:12
@miracle173: from the doc: If you omit the START WITH value, then the database determines the first automatic refresh time by evaluating the NEXT expression with respect to the creation time of the materialized view – Vincent Malgrat Apr 12 '12 at 15:22
@Vincent Malgrat: yes, you are right, i have missed this – miracle173 Apr 12 '12 at 17:21
sir me using DBMS_MVIEW.REFRESH to refresh the materialized view. so i cant need next and start with. – hafiz zahid Apr 13 '12 at 4:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.