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