Skip to Main Content
  • Questions
  • Synchronous refresh cannot be used for the non-partitioned tables?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Dejan.

Asked: August 25, 2016 - 8:36 am UTC

Last updated: August 26, 2016 - 1:33 pm UTC

Version: 12.0.1.2

Viewed 1000+ times

You Asked

Hi,

as I want to use the new technology in 12c called "Synchronous refresh" of the MView or group of MViews, I read the documentation ( https://docs.oracle.com/database/121/DWHSG/sync.htm#DWHSG029 ), but for me it's not 100% clear what are the restrictions and conditions to be able to use it.
In my test case, I created a simple dummy non-partitioned table and tried to register the Mview for the synchronous refresh, but unofortunately, I got the error message:

ORA-31905: Synchronous refresh cannot be used for materialized view "SDO"."MV1"
QSM-03205: The materialized view fails the join graph eligibility check for
synchronous refresh.
ORA-06512: at "SYS.DBMS_SYNC_REFRESH", line 38
ORA-06512: at "SYS.DBMS_SYNC_REFRESH", line 265
ORA-06512: at "SYS.DBMS_SYNC_REFRESH", line 283
ORA-06512: at line 2 


Here is the test case:

drop materialized view mv1;

drop materialized view log on t1;

drop table t1 purge;
create table t1(id number, datum date, string varchar2(64));

insert /*+ append NO_GATHER_OPTIMIZER_STATISTICS */
into t1(id, datum, string) 
select level, trunc(sysdate-(1000/level)), level || ' dummy'
from dual
connect by level <=1e3
;
    
commit;

alter table t1 add constraint pk_t1 primary key (id);

create materialized view log on t1 FOR SYNCHRONOUS REFRESH USING staglog1;

create materialized view mv1
REFRESH USING TRUSTED CONSTRAINTS
as
select min(id), datum,
count(id) as cnt_id, count(datum) as cnt_dat,
count(*) as cnt
from t1
group by datum
;

begin
  dbms_sync_refresh.register_mviews('MV1');
end;
/



I found also these MOS notes:
How to do a Synchronous Refresh with Staging Logs in Oracle 12c (Doc ID 1488598.1)
Overview of the Synchronous Refresh Demo in Oracle 12c (Doc ID 1484449.1)

and I run also the demo from it (aside from that, that the demo scripts are a little bit buggy and need changes/customizations), which worked fine, but it uses partitioned table in it.

So, my question is - is it possible at all to use this technology also for non-partitioned tables or not?

Thank you & kind regards
Dejan

and Chris said...

The requirements for synchronous refresh are more complex than "partitioned or not"!

You check whether a table is eligible for sync refresh with dbms_sync_refresh.can_syncref_table. This will tell you why it's not possible.

For example, using your test case we get:

CREATE TABLE SYNCREF_TABLE (
   statement_id    VARCHAR2(30),
   schema_name     VARCHAR2(30),
   table_name      VARCHAR2(30),
   mv_schema_name  VARCHAR2(30),
   mv_name         VARCHAR2(30),
   eligible        VARCHAR2(1),
   seq_num         NUMBER,
   msg_number      NUMBER,
   message         VARCHAR2(4000));
  
exec dbms_sync_refresh.can_syncref_table(user, 'T1', 'SRTest');
select eligible, message from SYNCREF_TABLE;

ELIGIBLE  MESSAGE                                                                                
N         The table CHRIS.T1 is classified as neither a fact nor a dimension table.              
N         The materialized view fails the join graph eligibility check for synchronous refresh.  


So a key point is you need more than one table. Otherwise Oracle has nothing to determine fact vs. dimension. The fact table will have foreign keys to all the dimension tables in the staging log.

The dimension tables don't need to be partitioned. But the fact table does. From the docs:

The materialized view must be partitioned along the same dimension as the fact table.

The partition key of the fact table should functionally determine the partition key of the materialized view.


There are various other restrictions on what's possible:

https://docs.oracle.com/database/121/DWHSG/sync.htm#DWHSG9090

Rating

  (1 rating)

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

Comments

Dejan T, August 26, 2016 - 12:38 pm UTC

Hi,
thanks for the clarification.
If I understand correctly, apart from other restrictions, synchronous refresh is not possible on a group of non-partitioned tables !?

Kind regards
Dejan
Chris Saxon
August 26, 2016 - 1:33 pm UTC

Yes. At least the MV and the fact table must be partitioned.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.