Doubt Materialized View Fast, Force, Complete

[meguelito]

Good morning guys, I'm here again and today with an easy question I guess I.

wanted to know which difference to create a materialized view with refresh fast or with refresh force and with refresh complete, because I am trying to create a refresh fast and tell me that I can not create with a more advanced, and documentation tell me to put Force or Complete.

[dr_gori]

Yeah: there are a number of restrictions for creating materialized views. In this link is being spoken exactly about this: [[0] http://en.glufke.net/oracle/viewtopic.php?t=1720 (I do not know of color restrictions, you will have to consult the documentation).

[meguelito]

Yes the restrictions I know you have but I wanted to know what the difference between the three ways only this, I know that fast gets only the new records and the complete get it all every time and the force does what ??

[Yana Disconzi]

Answering your question:

Select all

 
BUILD [IMMEDIATE "A view é preenchida imediatamente" 
	   DEFERRED]  "A view é preenchida no proximo refresh" 
REFRESH [FAST   "Insere ou exclui as linhas que foram alteradas-  precisa definir MATERI[code][code]

/ Code] Alized View Log "
Complete" Truncate the table and make the new INSERT "
Force " Oracle tries to make a fast refresh if not It is possible, then, complete "

ON [Commit" Update should occur whenever the database commits a transaction in one of the main table of materialized view "
Demand "Oracle will not update the materialized view, unless the User runs the update via DBMS_MVIEW"
Never "does not update"
Start with "Expected a date or time or time for automatic update "
Next
[[Enable | Disable] Query rewrite]

[/ Code]

[edutavaresjiva]

From the friend's doubt about the differences between Fast, Force and Complete, I would like someone to help me.

I created a VM on a customer who brings all the sales made, with his items, clients, etc ... This VM is updated daily at 1 hour in the morning and feeds the various analysis dashboards commercials.

My doubt has always been if over the years this processing at dawn every day would consume a lot of resource from the database (Oracle Database 12C Standard Edition Release 12.2.0.1.0) point of "overthrowing" the system.

I configured VM so that Refresh Complete. Today came a question in knowing how long, these 7 months of information is taking time to process completely. I understand that the complete is to reprocess everything every day. But when I opened the Toad and see the information of the VM, I checked that Last Refresh was 02/08/2019 01:00:18. If the update starts at 1 hour, I spent to see that the processing lasted 18 seconds. I went looking for and saw this topic and enjoying it, I would like to know if Refresh Complete just makes the new information complement on the base that is already there or if it really processes everything!?

VM parameters:

Select all

NOCACHE 
LOGGING 
NOCOMPRESS 
NOPARALLEL 
BUILD IMMEDIATE 
REFRESH COMPLETE 
START WITH TO_DATE('03-ago-2019 01:00:00','dd-mon-yyyy hh24:mi:ss') 
NEXT (TRUNC (SYSDATE+1) + 1/24 )  
WITH PRIMARY KEY

[dr_gori]

Here is a very interesting link on this subject, generation time, refresh complete, etc. https://technology.amis.nl/2014/08/15/m ... -recreate/ He suggests that it is faster to drop and recreate the MV, than to do Refresh Complete ... This is his opinion, I I did not stop to think if this really is worth it.

and at this link, has an example that shows that Refresh Complete actually changes the rowids of the lines : http://www.sqlsnippets.com/en/topic-12876.html

Materialized Views
Refresh Complete
There are Various Ways to Refresh The Date In Materialized View, The SimpleSt Way Being Complete Refresh. When the Complete Refresh Occurs The Materialized View's Defining Query Is Executed and The Entire Result Replaces The Date Currently Resident In The MaterialIged View. The Refresh Complete Clause Tells Oracle to Perform Complete Refreshes by Default When Materialized View is refreshed.

Select all

create materialized view mv 
  REFRESH COMPLETE 
  as select * from t 
;

Let's See Complete Refresh In Action Now. We will use The DBMS_MVIEW.Refresh Procedure to Initiate It. The "List" Parameter Accepts A list of Materialized Views to Refresh (in Our Case We Only has One) and the "Method" Parameter Accepts A "C", for Complete Refresh.

Select all

select key, val, rowid from mv ;

Select all

       KEY VAL   ROWID 
---------- ----- ------------------ 
         1 a     AAAWgHAAEAAAAIEAAA 
         2 b     AAAWgHAAEAAAAIEAAB 
         3 c     AAAWgHAAEAAAAIEAAC 
         4       AAAWgHAAEAAAAIEAAD

Select all

execute DBMS_MVIEW.REFRESH( LIST => 'MV', METHOD => 'C' ); 
  
select key, val, rowid from mv ;

Select all

       KEY VAL   ROWID 
---------- ----- ------------------ 
         1 a     AAAWgHAAEAAAAIEAAE 
         2 b     AAAWgHAAEAAAAIEAAF 
         3 c     AAAWgHAAEAAAAIEAAG 
         4       AAAWgHAAEAAAAIEAAH

Note How The Rowids In The Second Query Differ from Those Of The First, Even Though The Date In Table T Was Unchanged Throughout. This is Because Complete Refreshes Create to Whole New Set of Date, Even When The New Result Identical to the Old One.

IF Materialized View Contains Many Rows and The Base Table's Rows Change Infrequently Refreshing The Materialized View Completely Can Be An Expensive Operation. In Such Cases It Would Be Better to Process Only The Changed Rows. We Will Explore this Type of Refresh Next.

[edutavaresjiva]

These links helped me a lot. Thanks!

I understood the systematics of Refresh Complete and Fast.

I adjusted the VM to FAST and created the VM Log to temporarily store the information that have changed. Now only new, edited or excluded records will be incremented in VM.

VM Log:

Select all

CREATE MATERIALIZED VIEW LOG ON BASEPRD.TABELA 
TABLESPACE BASEPRD 
PCTUSED    0 
PCTFREE    10 
INITRANS   2 
MAXTRANS   255 
STORAGE    ( 
            INITIAL          64K 
            NEXT             1M 
            MINEXTENTS       1 
            MAXEXTENTS       UNLIMITED 
            PCTINCREASE      0 
            BUFFER_POOL      DEFAULT 
           ) 
NOCACHE 
LOGGING 
NOPARALLEL 
WITH ROWID 
EXCLUDING NEW VALUES;