CODEWITHSUNDEEP
sqloracle-databasejoin
Nosairat
Nosairat

Reputation: 492

fast refresh Materialized view with primary key based log

I have 2 tables

car_acc with primary key car_acc_id

and

final_cost with primary key car_acc_id references the previous one.

I have create 2 logs as:

CREATE MATERIALIZED VIEW LOG ON car_acc WITH PRIMARY KEY; 

CREATE MATERIALIZED VIEW LOG ON final_cost WITH PRIMARY KEY ;

I choose PrimaryKey log to solve import/export problems with ROW ID

then I've tried to create MATERIALIZED view on these tables as:

CREATE MATERIALIZED VIEW costs
BUILD IMMEDIATE
REFRESH FAST ON demand
AS SELECT ca.car_id, ca.acc_id,f.cost
  FROM car_acc ca,final_cost f
  WHERE ca.car_acc_id=f.car_acc_id;

but with exception :

"cannot fast refresh materialized view %s.%s"
*Cause:    Either ROWIDs of certain tables were missing in the definition or
       the inner table of an outer join did not have UNIQUE constraints on
       join columns.

Upvotes: 2

Views: 3567

Answers (1)

Patrick Bacon
Patrick Bacon

Reputation: 4640

You are close (and the exception does document the problem). With the 11g documentation on join materialized views with no aggregates in the section,Materialized Views Containing Only Joins, you need to have a materialized view log with the rowid included.

Thus I would drop the existing materialized view logs and create the following materialized view logs as follows:

CREATE MATERIALIZED VIEW LOG ON car_acc
WITH ROWID;


CREATE MATERIALIZED VIEW LOG ON final_cost
WITH ROWID;

Also (per the aforementioned link above), you will need to add the rowid in the select statement of the materialized view as follows:

CREATE MATERIALIZED VIEW costs
BUILD IMMEDIATE
REFRESH FAST ON demand
AS SELECT 
ca.rowid car_rid, f.rowid f_rowid,
ca.car_id, ca.acc_id,f.cost
  FROM car_acc ca,final_cost f
  WHERE ca.car_acc_id=f.car_acc_id;

Upvotes: 3

Related Questions