Skip to Main Content
  • Questions
  • does DBMS_REFRESH.REFRESH a atomic refresh of mviews?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Man.

Asked: October 14, 2016 - 11:51 am UTC

Last updated: October 17, 2016 - 12:46 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hello Tom,

we have a materialized view, created with
...
CREATE MATERIALIZED VIEW "mySchema"."myMView" ("Col1", "Col2", "Col3")
...
REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT (sysdate +10/1440)
...


which automatically creates a DBMS_JOB that executes
dbms_refresh.refresh('"mySchema"."myMView"');



Does DBMS_REFRESH.REFRESH an atomic refresh of the MView?

According to https://docs.oracle.com/database/121/REPMA/rarrefreshpac.htm#REPMA602 :
"REFRESH Procedure
This procedure manually refreshes a refresh group."

But from this doc I can't see if the refresh is atomic (DELETE+INSERT in single transaction) or non-atomic (TRUNCATE+INSERT).


Many thanks in advance.


and Connor said...

The docs say this:

"Refresh Groups
To preserve referential integrity and transactional (read) consistency among multiple materialized views, Oracle Database can refresh individual materialized views as part of a refresh group. After refreshing all of the materialized views in a refresh group, the data of all materialized views in the group correspond to the same transactionally consistent point in time."

Of course, that could still mean they do a truncate, and the refresh them all to a consistent point in time. So lets do a trace:

SQL> create materialized view t
  2  refresh complete on demand
  3  as select * from dba_objects;

Materialized view created.

SQL> exec dbms_refresh.make('RG','MCDONAC.T',sysdate+10,'sysdate+30')

PL/SQL procedure successfully completed.

SQL> exec dbms_monitor.session_trace_enable;

PL/SQL procedure successfully completed.

SQL> exec dbms_refresh.refresh('RG');

PL/SQL procedure successfully completed.

SQL> exec dbms_monitor.session_trace_disable;

PL/SQL procedure successfully completed.



The resulting trace file contains:

delete from "MCDONAC"."T"

INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "MCDONAC"."T" select * from dba_objects



and we dont see any evidence of commits until after both steps are done:

XCTEND rlbk=0, rd_only=0, tim=166305987242


Hope this helps

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Additional question

A reader, October 16, 2016 - 4:47 pm UTC

Could you pls explain why oracle used the hint BYPASS_RECURSIVE_CHECK ?
Could it be used for user defined queries ? Why not? What for if yes?
Connor McDonald
October 17, 2016 - 12:46 am UTC

There's a reason why some of the hints are not included in the documentation :-)


and so?

A reader, October 17, 2016 - 8:06 am UTC

there is a reason; yes;
Oracle has its own resaon;
what is (are) the reson(s) upon to your own consideration ?
mean your opinion of what could be those reasons?
do you have opinion?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library