Hi Tom,
I was trying to prepare materialized view against some table which are periodically dropped and recreated. I did not find an option to automatically refresh the materialized view in this case. Refresh FAST ON COMMIT is not working, and REFRESH ON DEMAND with start time is not working as well. Can you please suggest if it is possible and if yes, how? It would be great if you could help with this!
Example codes:
CREATE TABLE test_table (ID VARCHAR2(20) PRIMARY KEY);
INSERT INTO test_table VALUES ('1');
INSERT INTO test_table VALUES ('2');
INSERT INTO test_table VALUES ('3');
COMMIT;
CREATE MATERIALIZED VIEW LOG ON test_table;
CREATE MATERIALIZED VIEW test_table_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT * FROM test_table;
SELECT * FROM test_table_mv; --3 rows selected
TRUNCATE TABLE test_table;
SELECT * FROM test_table_mv; --again 3 rows selected
Well, truncating will refresh the MV with any outstanding changes up to that point:
CREATE TABLE test_table (ID VARCHAR2(20) PRIMARY KEY);
INSERT INTO test_table VALUES ('1');
INSERT INTO test_table VALUES ('2');
INSERT INTO test_table VALUES ('3');
COMMIT;
CREATE MATERIALIZED VIEW LOG ON test_table;
CREATE MATERIALIZED VIEW test_table_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT * FROM test_table;
SELECT * FROM test_table_mv;
ID
1
2
3
insert into test_table values (4);
TRUNCATE TABLE test_table;
SELECT * FROM test_table_mv;
ID
1
2
3
4 But as you say, it won't remove the existing rows.
Try to fast refresh it and you'll see why:
exec dbms_mview.refresh('test_table_mv', 'F');
ORA-32321: REFRESH FAST of "CHRIS"."TEST_TABLE_MV" unsupported after detail table TRUNCATEThe operation isn't supported! The problem is truncate bypasses the materialized view log. So there are no delete entries to tell the database what's happened.
Of course, if you've just truncated a table then presumably the MV has no rows in it too! So a complete refresh should be nice and quick. After which you're on your way again:
exec dbms_mview.refresh('test_table_mv', 'C');
insert into test_table values (5);
commit;
select * from test_table_mv;
ID
5 Similarly, when you drop and recreate a table you've severed the link between the MV and the table. And the MV log is gone too!
Though once again, all you need is a complete refresh to be back in action:
drop table test_table cascade constraints purge;
CREATE TABLE test_table (ID VARCHAR2(20) PRIMARY KEY);
CREATE MATERIALIZED VIEW LOG ON test_table;
exec dbms_mview.refresh('test_table_mv', 'C');
insert into test_table values (6);
commit;
select * from test_table_mv;
ID
6