Data Integrity for Fast Refresh Materialized Views with Aggregates and Updates/Deletes
Update: thanks to Connor McDonald for pointing out how to fix this issue (see "The Solution" and subsequent explanation below). Connor wrote a followup post based on this article discussing count(*) vs count(1) differences.
The Problem
I recently had...
talkapex.com5 min read
Here's a hack :-)
Add a GROUP BY and a COUNT
SQL> create table demo_emps ( 2 id number primary key, 3 name varchar2(255), 4 sal number 5 ); Table created. SQL> SQL> insert into demo_emps values(1, 'martin', 100); 1 row created. SQL> SQL> create materialized view log on demo_emps 2 with rowid, sequence, commit scn, primary key (sal) 3 including new values; Materialized view log created. SQL> SQL> create materialized view demo_emps_mv 2 refresh fast on commit 3 as 4 select 1 grp, max(sal) max_sal, count(*) c 5 from demo_emps 6 group by 1 7 ; Materialized view created. SQL> SQL> select * 2 from demo_emps_mv 3 ; GRP MAX_SAL C ---------- ---------- ---------- 1 100 1 SQL> SQL> insert into demo_emps values(2, 'sally', 200); 1 row created. SQL> SQL> commit; Commit complete. SQL> SQL> select * 2 from demo_emps_mv 3 ; GRP MAX_SAL C ---------- ---------- ---------- 1 200 2 SQL> SQL> select staleness 2 from user_mviews 3 where 1=1 4 and mview_name = 'DEMO_EMPS_MV' 5 ; STALENESS ------------------- FRESH SQL> SQL> update demo_emps 2 set sal = sal + 1 3 ; 2 rows updated. SQL> SQL> commit; Commit complete. SQL> SQL> select * 2 from demo_emps_mv 3 ; GRP MAX_SAL C ---------- ---------- ---------- 1 201 2 SQL> SQL> select staleness 2 from user_mviews 3 where 1=1 4 and mview_name = 'DEMO_EMPS_MV' 5 ; STALENESS ------------------- FRESH SQL> SQL>