Hi,
I've been bashing my head all day with trying to create some materialized views. I've made some progress, but have now hit a brick wall, sadly!
Basically, I've been asked to take a view and see if I can get performance benefits by turning all or some of it into materialized views. Because the underlying config tables are updated fairly frequently, I want the materialized views to fast refresh on commit. However, when I try to create a materialized view containing an outer join from a table to an aggregated materialized view, I get ORA-12052: cannot fast refresh materialized view.
I've gone through the documentation and also @"Rob van Wijk"'s very handy series of blogs on the subject (especially http://rwijk.blogspot.co.uk/2009/09/fast-refreshable-materialized-view.html) but have not found anything that matches. Maybe I've missed something somewhere along the line, or maybe I'm just asking for something totally impossible?
My db is 11.2.0.2.
Here's the test scripts I've been working with:
drop materialized view test1_test2_mv;
drop materialized view log on test2;
drop table test2;
drop materialized view log on test1_mv;
drop materialized view test1_mv;
drop materialized view log on test1;
drop table test1;
create table test1 (id number,
type varchar2(10),
val number,
update_time date,
constraint t1_pk primary key (id, type, val));
insert into test1
select 1, 'a', 1001, sysdate - 10/24 from dual union all
select 1, 'b', 1003, sysdate - 9/24 from dual union all
select 1, 'c', 1002, sysdate - 8/24 from dual union all
select 1, 'd', 1004, sysdate - 7/24 from dual union all
select 1, 'e', 1005, sysdate - 6/24 from dual union all
select 1, 'c', 1006, sysdate - 5/24 from dual union all
select 2, 'a', 1002, sysdate - 4/24 from dual union all
select 2, 'b', 1005, sysdate - 3/24 from dual union all
select 3, 'a', 1001, sysdate - 2/24 from dual union all
select 3, 'c', 1006, sysdate - 1/24 from dual union all
select 3, 'e', 1008, sysdate - 2/24 from dual union all
select 4, 'd', 1004, sysdate - 3/24 from dual union all
select 5, 'b', 1002, sysdate - 4/24 from dual union all
select 5, 'g', 1001, sysdate - 5/24 from dual union all
select 6, 'h', 1004, sysdate - 6/24 from dual union all
select 7, 'b', 1007, sysdate - 7/24 from dual union all
select 7, 'd', 1001, sysdate - 8/24 from dual;
commit;
select * from test1;
CREATE MATERIALIZED VIEW LOG ON test1
WITH rowid, primary key (update_time)
including new values;
CREATE MATERIALIZED VIEW test1_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT id,
MAX (case when type = 'a' then val end) AS col_a,
MAX (case when type = 'b' then val end) AS col_b,
MAX (case when type = 'c' then val end) AS col_c,
MAX (case when type = 'd' then val end) AS col_d,
MAX (update_time) AS update_time
FROM test1
WHERE TYPE IN ('a',
'b',
'c',
'd')
GROUP BY id;
CREATE MATERIALIZED VIEW LOG ON test1_mv
WITH rowid
including new values;
create table test2 (id number,
col2 number,
col3 varchar2(10),
col4 number,
constraint t2_pk primary key (id));
insert into test2
select 1, 1, 'bob', 1 from dual union all
select 2, 1, 'sue', 1 from dual union all
select 3, 1, 'tom', 1 from dual union all
select 4, 1, 'jay', 1 from dual union all
select 5, 1, 'art', 1 from dual union all
select 6, 1, 'kay', 1 from dual union all
select 7, 1, 'max', 1 from dual union all
select 8, 1, 'tim', 1 from dual union all
select 9, 1, 'liz', 1 from dual;
commit;
CREATE MATERIALIZED VIEW LOG ON test2
WITH rowid, primary key
including new values;
CREATE MATERIALIZED VIEW test1_test2_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT t2.rowid,
t1.id,
t1.col_a,
t1.col_b,
t1.col_c,
t1.col_d,
t1.update_time,
t2.col2,
t2.col3
FROM test1_mv t1,
test2 t2
WHERE t1.id (+ ) = t2.id; -- outer join symbol doesn't display properly on the forums without the space, grr!
ORA-12052: cannot fast refresh materialized view TEST1_TEST2_MV
Is there any way I can get the materialized view to fast refresh on commit or am I asking the impossible?