0

Recently I ran DBMS_ADVISOR.TUNE_MVIEW and it gives me several lines to

CREATE MATERIALIZED VIEW LOG ON "MYSCHEMA"."COMPANY" WITH ROWID   
CREATE MATERIALIZED VIEW LOG ON "MYSCHEMA"."BOARD" WITH ROWID  
....
CREATE MATERIALIZED VIEW LOG ON "MYSCHEMA"."CONTROL" WITH ROWID  

and the script to create materialized view is pretty simple with multiple joins and conditions:

  CREATE MATERIALIZED VIEW V_SUMBAL_AMT 
  BUILD IMMEDIATE REFRESH FAST START WITH (sysdate) NEXT (sysdate+1*60/(60*60*24)) WITH rowid AS  
  SELECT sb.id as id,  
  SUM(c.OPEN_DAY) as open_day, sum(c.OPEN_NIGHT) as open_night, 
  SUM(c.CLOS_DAY) as clos_day, sum(c.CLOS_UNV) as clos_night  
  FROM company c  
  join board b on c.company_id = b.id  
  join summaryBoard sb on sb.id = b.summary_id  
  join control cl on cl.id = c.control_id  
  where cl.ignored <> 1 and c.control_id IN (SELECT cm.control_id from control_manager cm where cm.manager_id = b.manager_id)
  GROUP BY sb.id  

I know it looks a little messy, just give an idea about how the view looks like. My question is shall I follow the instruction to create view log? And what is the cost if the dba will never manager the view log so what is the cost and impact to the database?

  • 1
    MV log is used to track changes in the table on which it is created. Once MV is refreshed, this MV log entries get cleaned out automatically. Normally it won't be noticeable unless you create MV log for tables with very (I mean very) high OLTP DML. If you want to test it out, go ahead with advisor recommendations. – Raj Jul 31 '14 at 18:59
  • @Raj Thank you! So if the MV is refreshed every 20 seconds, then related table's mv logs are going to clean out after every 20 seconds? So my understanding is, the log just record the last activity then that's it? – Dreamer Jul 31 '14 at 19:03
  • 1
    these logs record all dml on that table since last time MV refresh ran successfully, it provides a delta for MV refresh, otherwise your MV refresh could take a long time if it had to scan everything. – Raj Jul 31 '14 at 19:08
  • @Raj Thanks again. Last question is if the MV is set true to query rewrite, so what is the performance impact if MV log is set as well? I think the query rewrite should not effect the log refreshing but only by Oracle engine, is this correct? – Dreamer Jul 31 '14 at 19:15

Your Answer

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

Browse other questions tagged or ask your own question.