2

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' 
...
  • 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
4

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
  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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