---
title: "Discussion on \"Data Integrity for Fast Refresh Materialized Views with Aggregates and Updates/Deletes\" | Hashnode"
description: "Discussion on \"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.\nThe Problem\nI recently had..."
url: "https://hashnode.com/posts/data-integrity-for-fast-refresh-materialized-views-with-aggregates-and-updatesdeletes/659be3d033228dd6ee298db5"
---

[Feed](https://hashnode.com/feed) /

Discussion

# [Data Integrity for Fast Refresh Materialized Views with Aggregates and Updates/Deletes](https://talkapex.com/data-integrity-for-fast-refresh-materialized-views-with-aggregates-and-updatesdeletes?utm_source=hashnode&utm_medium=feed)

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...

Jan 8, 2024 · 5 min read · talkapex.com  
Updated Jan 14, 2024  
Read the article

-   [#oracle](https://hashnode.com/tag/oracle)
-   [#oracle-sql](https://hashnode.com/tag/oracle-sql)

## Written by

[Martin Giffy D'Souza](https://hashnode.com/@martindsouza)

@martindsouza · Calgary, Alberta, Canada

  
[View profile](https://hashnode.com/@martindsouza)

Oracle APEX Developer

## Responses 1

Markdown supported · ⌘↵ to post

[Connor McDonald](https://hashnode.com/@connormcdonald)

[Jan 8, 2024](https://hashnode.com/posts/data-integrity-for-fast-refresh-materialized-views-with-aggregates-and-updatesdeletes/659be3d033228dd6ee298db5/comment/659c0b4bb183b8ce2c36dc37)

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>
```