Materialized View

Question

We are working on materialized view which is successfully created when we refresh FORCE or COMPLETE but it does not create MV when we refresh FAST. The error it always "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." OR "Neither ROWIDs and nor primary key constraints are supported for complex queries."

Below is the query -

-- MATERIALIZED VIEW log for A

CREATE MATERIALIZED VIEW LOG ON SCHEMA.A
TABLESPACE A_SCHEMA_DATA
WITH PRIMARY KEY
INCLUDING NEW VALUES;

-- MATERIALIZED VIEW log for B
CREATE MATERIALIZED VIEW LOG ON A_SCHEMA.B
TABLESPACE A_SCHEMA_DATA 
WITH PRIMARY KEY
INCLUDING NEW VALUES;


-- MATERIALIZED VIEW Query
CREATE MATERIALIZED VIEW A_SCHEMA.MV_A1
BUILD IMMEDIATE 
REFRESH FAST ON DEMAND
AS 

  SELECT * FROM (
   SELECT 
        A.T_ID,
        B.NAME AS NAME,
        B.ANS AS ANS
   FROM A_SCHEMA.A A, A_SCHEMA.B B
   WHERE A.T_ID = B.T_ID AND 
   B.NAME IN ('Order', 'Price')
 )
 PIVOT
 (
   MAX(to_char(SUBSTR(ANS, 0,100)))
   FOR NAME IN ('Order' ORDER, 'Price' PRICE)
 )
ORDER BY A.CREATED_DATE BY DESC;

Jon Heller Jon Heller · Accepted Answer · 2020-05-08T04:46:43

Use MAX(DECODE... instead of PIVOT to work around the error "ORA-12015: cannot create a fast refresh materialized view from a complex query".

Fast refresh materialized views frequently require using older versions of features, and the queries will often end up looking ugly. For example, we have to use the old fashioned (+) join syntax instead of ANSI joins. PIVOT is no good, and even the MAX(CASE WHEN... pattern doesn't work in 11g. You also need to add some settings to the materialized view logs, as described below.

Sample Schema

--drop table a;
--drop table b;
--drop materialized view mv_a1;

create table a(t_id number primary key);
create materialized view log on a with sequence,rowid(t_id) including new values;

create table b(t_id number, name varchar2(100), ans varchar2(100));
create materialized view log on b with sequence,rowid(t_id,name,ans) including new values;

insert into a values(1);
insert into a values(2);
insert into b values(1, 'Order', 'A');
insert into b values(2, 'Price', 'A');
insert into b values(2, 'Price', 'B');

create materialized view mv_a1
build immediate 
refresh fast on commit
AS 
select
    a.t_id,
    max(decode(name, 'Order', to_char(substr(ans, 0,100)))) the_order,
    max(decode(name, 'Price', to_char(substr(ans, 0,100)))) price
    --In old versions (11g and below?), extra COUNTs were necessary.
    --See: https://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#DWHSG0082
    ,count(*) the_count, count(a.t_id) count_t_id, count(name) count_name, count(ans) count_ans
from a, b
where a.t_id = b.t_id
group by a.t_id;

Materialized View - FAST refresh not able to create due to complex query

1 Answers

Sample Schema

Materialized View