Create fast-refresh MV over dblink on table without PK

dblinkfast-refreshmaterialized-vieworacleprimary-key

I want to create a materialized view between a primary system (Oracle 18c) and an external system (Oracle 19c) via a dblink.

  • The MV would refresh on a 30-second schedule (since on-commit is not possible over a dblink).
  • The table in the external system does not have a primary key.

I'm a novice. Here's what I've tried:

--in the external system:  
create materialized view log on external_system.workorder with rowid;  


--in the primary system:
create materialized view primary_system.workorder_mv
build immediate 
refresh fast 
start with sysdate next sysdate + (30/(60*60*24))
as
select
    cast(workorderid as number(38,0)) as objectid,
    wonum,
    status,
    --other fields
    longitudex,
    latitudey
from
    external_system.workorder@gistomax

When I try the above, I get a litany of errors, starting with one that says I need a primary key.

I found a page that suggests that it's possible to do a fast refresh over a dblink. But the example uses a primary key, not a rowid.

And the docs say:

Rowid materialized views are not eligible for fast refresh after a
master table reorganization until a complete refresh has been
performed.

But I don't really know what that means. And to be honest, I'm so new at this, that I know I'm barking up the wrong tree, so I'll stop there.

Question:

Is it possible to create a fast-refresh MV over a dblink on table that doesn't have a PK?

Best Answer

As already demonstrated in your other question, a unique constraint on a NOT NULL column is good enough to create an mview log with "PK" values. That can be used for fast refresh.

SQL> conn bp/bp
Connected.
SQL> create database link self connect to bp identified by bp
     using 'localhost:1521/RYMIN19';

Database link created.

SQL> create table t1 (c1 number not null unique, c2 number);

Table created.

SQL> create materialized view log on t1 with primary key;

Materialized view log created.

SQL> create materialized view mv1 refresh fast on demand as select * from t1@self;

Materialized view created.

SQL> insert into t1 values (1, 1);

1 row created.

SQL> insert into t1 values (2, 2);

1 row created.

SQL> commit;

Commit complete.

Then:

SQL> exec dbms_mview.refresh('MV1', 'f');

PL/SQL procedure successfully completed.

SQL> select last_refresh_type from user_mviews where mview_name = 'MV1';

LAST_REF
--------
FAST
Related Question