Wrong result with REAL-TIME MVIEW after refresh (Doc ID 2488455.1)

Last updated on MARCH 10, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 to 12.2.0.1 [Release 12.2]
Information in this document applies to any platform.

Symptoms

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

create table t1 (id integer not null, w varchar2(2) not null, t_id integer,
constraint t1pk primary key(id));
insert into t1 values (0, 'W1', null);
insert into t1 values (1, 'W1', 1000);
insert into t1 values (2, 'W2', 1001);
insert into t1 values (3, 'W2', 1000);
commit;

CREATE MATERIALIZED VIEW LOG ON t1
WITH ROWID (w,t_id)
INCLUDING NEW VALUES
/

create materialized view mv_t1
build immediate
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
ENABLE ON QUERY COMPUTATION
AS
select count(*) c,
count(case when w = 'W1' then 1 else null end) CW1,
count(case when w = 'W2' then 1 else null end) CW2,
t_id
from t1
where t_id is not null
group by t_id
/
SQL> select * from mv_t1;
C CW1 CW2 T_ID
---------- ---------- ---------- ----------
2 1 1 1000
1 0 1 1001

SQL> select /*+ FRESH_MV */ * from mv_t1;
C CW1 CW2 T_ID
---------- ---------- ---------- ----------
2 1 1 1000
1 0 1 1001

insert into t1 values (4, 'W3', 1000);
commit;

SQL> select * from mv_t1;
C CW1 CW2 T_ID
---------- ---------- ---------- ----------
2 1 1 1000
1 0 1 1001

SQL> select /*+ FRESH_MV */ * from mv_t1;
C CW1 CW2 T_ID
---------- ---------- ---------- ----------
1001 1 0 1
1000 3 1 1

PLANS:

-- 1. After insert into T1

SQL> set autotrace on explain
SQL> select * from mv_t1;

C CW1 CW2 T_ID
---------- ---------- ---------- ----------
2 1 1 1000
1 0 1 1001

Execution Plan
----------------------------------------------------------
Plan hash value: 1469667240

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 26 | 3 (0)| 00:00:01
|
| 1 | MAT_VIEW ACCESS FULL| MV_T1 | 2 | 26 | 3 (0)| 00:00:01
|
------------------------------------------------------------------------------

SQL> select /*+ FRESH_MV */ * from mv_t1;

C CW1 CW2 T_ID
---------- ---------- ---------- ----------
1001 1 0 1
1000 3 1 1

Execution Plan
----------------------------------------------------------
Plan hash value: 25703917

------------------------------------------------------------------------------
-----------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
------------------------------------------------------------------------------
-----------------------
| 0 | SELECT STATEMENT | | 310 | 16120 |
18 (23)| 00:00:01 |
| 1 | VIEW | | 310 | 16120 |
18 (23)| 00:00:01 |
| 2 | UNION-ALL | | | |
| |
|* 3 | VIEW | VW_FOJ_0 | 100 | 5500 |
7 (15)| 00:00:01 |
|* 4 | HASH JOIN FULL OUTER | | 100 | 5400 |
7 (15)| 00:00:01 |
| 5 | VIEW | | 2 | 86 |
3 (0)| 00:00:01 |
| 6 | MAT_VIEW ACCESS FULL | MV_T1 | 2 | 26 |
3 (0)| 00:00:01 |
| 7 | VIEW | | 100 | 1100 |
4 (25)| 00:00:01 |
| 8 | HASH GROUP BY | | | |
4 (25)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | MLOG$_T1 | 1 | 25 |
3 (0)| 00:00:01 |
| 10 | VIEW | | 210 | 10920 |
11 (28)| 00:00:01 |
| 11 | UNION-ALL | | | |
| |
|* 12 | FILTER | | | |
| |
| 13 | NESTED LOOPS OUTER | | 200 | 13000 |
4 (25)| 00:00:01 |
| 14 | VIEW | | 100 | 5200 |
4 (25)| 00:00:01 |
|* 15 | FILTER | | | |
| |
| 16 | HASH GROUP BY | | | |
4 (25)| 00:00:01 |
|* 17 | TABLE ACCESS FULL | MLOG$_T1 | 1 | 25 |
3 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV_T1 | 2 | 26 |
0 (0)| 00:00:01 |
| 19 | MERGE JOIN | | 10 | 680 |
7 (29)| 00:00:01 |
| 20 | MAT_VIEW ACCESS BY INDEX ROWID| MV_T1 | 2 | 26 |
2 (0)| 00:00:01 |
| 21 | INDEX FULL SCAN | I_SNAP$_MV_T1 | 2 | |
1 (0)| 00:00:01 |
|* 22 | FILTER | | | |
| |
|* 23 | SORT JOIN | | 100 | 5500 |
5 (40)| 00:00:01 |
| 24 | VIEW | | 100 | 5500 |
4 (25)| 00:00:01 |
| 25 | SORT GROUP BY | | | |
4 (25)| 00:00:01 |
|* 26 | TABLE ACCESS FULL | MLOG$_T1 | 1 | 25 |
3 (0)| 00:00:01 |
------------------------------------------------------------------------------

SQL> select count(*), t_id from t1 where t_id is not null group by t_id;

COUNT(*) T_ID
---------- ----------
3 1000
1 1001

Execution Plan
----------------------------------------------------------
Plan hash value: 136660032

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 39 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 3 | 39 | 4 (25)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T1 | 3 | 39 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

-- 2. After DBMS_MVIEW.REFRESH

SQL> exec DBMS_MVIEW.REFRESH ('mv_t1', 'f');

PL/SQL procedure successfully completed.

SQL> set autotrace on explain
SQL> select * from mv_t1;

C CW1 CW2 T_ID
---------- ---------- ---------- ----------
3 1 1 1000
1 0 1 1001

Execution Plan
----------------------------------------------------------
Plan hash value: 1469667240

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 26 | 3 (0)| 00:00:01
|
| 1 | MAT_VIEW ACCESS FULL| MV_T1 | 2 | 26 | 3 (0)| 00:00:01
|
------------------------------------------------------------------------------

SQL> select /*+ FRESH_MV */ * from mv_t1;

C CW1 CW2 T_ID
---------- ---------- ---------- ----------
3 1 1 1000
1 0 1 1001

Execution Plan
----------------------------------------------------------
Plan hash value: 1469667240

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 26 | 3 (0)| 00:00:01
|
| 1 | MAT_VIEW ACCESS FULL| MV_T1 | 2 | 26 | 3 (0)| 00:00:01
|
------------------------------------------------------------------------------

SQL> select count(*), t_id from t1 where t_id is not null group by t_id;

COUNT(*) T_ID
---------- ----------
3 1000
1 1001

Execution Plan
----------------------------------------------------------
Plan hash value: 3308033062

------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 2 | 14 | 3 (0)|
00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV_T1 | 2 | 14 | 3 (0)|
00:00:01 |
------------------------------------------------------------------------------

-- 3. Again insert into T1

SQL> insert into t1 values (5, 'W2', 1000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mv_t1;

C CW1 CW2 T_ID
---------- ---------- ---------- ----------
3 1 1 1000
1 0 1 1001

Execution Plan
----------------------------------------------------------
Plan hash value: 1469667240

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 26 | 3 (0)| 00:00:01
|
| 1 | MAT_VIEW ACCESS FULL| MV_T1 | 2 | 26 | 3 (0)| 00:00:01
|
------------------------------------------------------------------------------

SQL> select /*+ FRESH_MV */ * from mv_t1;

C CW1 CW2 T_ID
---------- ---------- ---------- ----------
1001 1 0 1
1000 4 1 2

Execution Plan
----------------------------------------------------------
Plan hash value: 25703917

SQL> select count(*), t_id from t1 where t_id is not null group by t_id;

COUNT(*) T_ID
---------- ----------
4 1000
1 1001

Execution Plan
----------------------------------------------------------
Plan hash value: 136660032

Cause

	To view full details, sign in with your My Oracle Support account.
	Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Wrong result with REAL-TIME MVIEW after refresh (Doc ID 2488455.1)

Applies to:

Symptoms

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!