现象：物化视图对update不更新

现象：物化视图对update不更新
现象TESTCASE:
SQL> create table b (id number,name char(20));
insert into b values(1,'A');
 insert into b values(2,'B');
Table created.

SQL>
1 row created.

SQL>

1 row created.

SQL> commit;

Commit complete.
 

SQL> create materialized view log on b with rowid ,sequence(id,name) including new values;


SQL>

Materialized view log created.

SQL>
SQL>
SQL>
SQL> create materialized view mvb refresh fast on commit with rowid
2 as select id,name from b group by id,name;

Materialized view created.

SQL>
SQL> select * from mvb;

 ID NAME
---------- --------------------
 1 A
 2 B

SQL> insert into b values(3,'r');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mvb; --->  insert后，物化视图正常刷新

 ID NAME
---------- --------------------
 1 A
 2 B
 3 r


SQL> update b set name='Y';

3 rows updated.

SQL> commit;

Commit complete.

SQL> select * from mvb; ---> no refresh

 ID NAME
---------- --------------------
 1 A
 2 B
 3 r
SQL>
SQL> insert into b values(6,'t');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mvb; ---> no refresh

 ID NAME
---------- --------------------
 1 A
 2 B
 3 r

SQL>

原因：对于GROUP BY物化视图必须使用count(*),否则仅仅在insert之后刷新物化视图
The cause is COUNT(*) is not being used in MVIEW creation when aggregate functions are being used.
COUNT(*) must always be present to guarantee all types of fast refresh. Otherwise, you may be limited to fast refresh after inserts only.

附一: MVIEW诊断

SQL> @?/rdbms/admin/utlxmv.sql
SQL> truncate table MV_CAPABILITIES_TABLE;
SQL> select capability_name, possible, msgtxt from mv_capabilities_table where capability_name like 'REFRESH%' order by seq;

CAPABILITY_NAME P MSGTXT
------------------------------ - ----------------------------------------------------------------------------------------------------
REFRESH_COMPLETE Y
REFRESH_FAST Y
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML N COUNT(*) is not present in the select list
REFRESH_FAST_AFTER_ONETAB_DML N GROUP BY clause is present but no aggregate functions are used
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled ==========> Cause
REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the materialized view

Solution:
--------------
Create Mview with COUNT(*) in select clause as below.

SQL> create materialized view mvb refresh fast on commit with rowid as select COUNT(*),id,name from b group by id,name;

附二：Materialized View (MVIEW) Not Reflecting Update：
MVIEW doesn't reflect the UPDATES even though "INCLUDING NEW VALUES" is mentioned.
Only the inserted records are being reflected.


 
-- Create table
SQL> CREATE TABLE ADP_CTM_LOG
2 (
3 LOG_PK NUMBER(10) CONSTRAINT NN_10020_ADP_CTM_LOG NOT NULL,
4 OFFICE_BIC_CODE VARCHAR2(16 BYTE) CONSTRAINT NN_10021_ADP_CTM_LOG NOT NULL,
5 REQUEST_REF_NO VARCHAR2(16 BYTE) CONSTRAINT NN_10022_ADP_CTM_LOG NOT NULL,
6 REQUEST_SENT_DATE DATE CONSTRAINT NN_10023_ADP_CTM_LOG NOT NULL,
7 REQUEST_TYPE VARCHAR2(30 BYTE) CONSTRAINT NN_10024_ADP_CTM_LOG NOT NULL,
8 RESPONSE_MIN_LAST_UPDATE_DATE DATE,
9 RESPONSE_ERROR_CODE VARCHAR2(40 BYTE),
10 APP_REGI_DATE DATE CONSTRAINT NN_10025_ADP_CTM_LOG NOT NULL,
11 APP_UPD_DATE DATE CONSTRAINT NN_10026_ADP_CTM_LOG NOT NULL,
12 CREATED_BY VARCHAR2(20 BYTE) CONSTRAINT NN_10027_ADP_CTM_LOG NOT NULL,
13 CREATION_DATE DATE CONSTRAINT NN_10028_ADP_CTM_LOG NOT NULL,
14 UPDATED_BY VARCHAR2(20 BYTE) CONSTRAINT NN_10029_ADP_CTM_LOG NOT NULL,
15 UPDATE_DATE DATE CONSTRAINT NN_10030_ADP_CTM_LOG NOT NULL
16 )
17 LOGGING
18 NOCOMPRESS
19 NOCACHE
20 NOPARALLEL
21 MONITORING;

Table created.

SQL> ALTER TABLE ADP_CTM_LOG ADD (CONSTRAINT PK_756_ADP_CTM_LOG PRIMARY KEY (LOG_PK));

Table altered.

-- Create MVIEW log
SQL> CREATE MATERIALIZED VIEW LOG ON adp_ctm_log
2 WITH ROWID , SEQUENCE(office_bic_code, response_min_last_update_date), primary key
3 INCLUDING NEW VALUES;

Materialized view log created.

-- Create MVIEW
SQL> CREATE MATERIALIZED VIEW adp_ctm_log_mv
2 BUILD IMMEDIATE
3 REFRESH FAST ON COMMIT
4 AS
5 SELECT office_bic_code, MAX(response_min_last_update_date) response_last_update_date
6 FROM adp_ctm_log
7 GROUP BY office_bic_code;

Materialized view created.

-- Check the date
SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
-------------------
20-02-2012 11:22:08

-- Check the MVIEW last refresh date
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'dd-mm-yyyy hh24:mi:ss') from user_mviews where mview_name = 'ADP_CTM_LOG_MV';

MVIEW_NAME TO_CHAR(LAST_REFRES
------------------------------ -------------------
ADP_CTM_LOG_MV 20-02-2012 11:22:03

-- Insert rerord into the table
SQL> INSERT INTO ADP_CTM_LOG ( LOG_PK, OFFICE_BIC_CODE, REQUEST_REF_NO, REQUEST_SENT_DATE, REQUEST_TYPE,
2 RESPONSE_MIN_LAST_UPDATE_DATE, RESPONSE_ERROR_CODE, APP_REGI_DATE, APP_UPD_DATE, CREATED_BY,
3 CREATION_DATE, UPDATED_BY, UPDATE_DATE ) VALUES (
4 17, 'TEST1', 'CRQ000000004', TO_Date( '12/28/2011 11:11:12 AM', 'MM/DD/YYYY HH:MI:SS AM')
5 , 'MultiTradeLevelRequest', TO_Date( '01/01/2012 12:07:47 PM', 'MM/DD/YYYY HH:MI:SS AM')
6 , NULL, TO_Date( '04/18/2011 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '04/18/2011 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
7 , 'SYSTEM', TO_Date( '12/28/2011 11:11:08 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'SYSTEM'
8 , TO_Date( '12/28/2011 11:11:08 AM', 'MM/DD/YYYY HH:MI:SS AM'));

1 row created.

SQL> COMMIT;

Commit complete.

-- Check the MVIEW log
SQL> select count(*) from mlog$_adp_ctm_log;

COUNT(*)
----------
0

-- Check MVIEW last refresh date. It should be changed, as MVIEW is ON COMMIT refresh. It has actually refreshed.
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'dd-mm-yyyy hh24:mi:ss') from user_mviews where mview_name = 'ADP_CTM_LOG_MV';

MVIEW_NAME TO_CHAR(LAST_REFRES
------------------------------ -------------------
ADP_CTM_LOG_MV 20-02-2012 11:22:23

SQL> update adp_ctm_log set response_min_last_update_date =sysdate +3 where log_pk=17;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from ADP_CTM_LOG_MV;

OFFICE_BIC_CODE RESPONSE_
---------------- ---------
TEST1 01-JAN-12

-- check the last refresh date. It should have changed as the MV is refresh on COMMIT, But it's actually not refreshed.
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'dd-mm-yyyy hh24:mi:ss') from user_mviews where mview_name = 'ADP_CTM_LOG_MV';

MVIEW_NAME TO_CHAR(LAST_REFRES
------------------------------ -------------------
ADP_CTM_LOG_MV 20-02-2012 11:22:23

SQL>
Why has the MVIEW not refreshed and not reflected the correct entry? To know this, see the next code section.
 
Cause
 
See the output of MV_CAPABILITIES_TABLE which explains why MVIEW was not refreshed on commit:


 
SQL> create table MV_CAPABILITIES_TABLE
2 (
3 STATEMENT_ID VARCHAR2(30),
4 MVNAME VARCHAR2(30),
5 CAPABILITY_NAME VARCHAR2(30),
6 POSSIBLE CHAR(1),
7 RELATED_TEXT VARCHAR2(2000),
8 RELATED_NUM NUMBER,
9 MSGNO INTEGER,
10 MSGTXT VARCHAR2(2000),
11 SEQ NUMBER,
12 MVOWNER VARCHAR2(30)
13 )
14 tablespace USERS
15 pctfree 10
16 initrans 1
17 maxtrans 255
18 storage
19 (
20 initial 64K
21 minextents 1
22 maxextents unlimited
23 );

Table created.

SQL> execute dbms_mview.explain_mview('adp_ctm_log_mv');

PL/SQL procedure successfully completed.

SQL> set linesize 150
SQL> col msgtxt for a50
SQL> select capability_name, possible, msgtxt from mv_capabilities_table where capability_name like 'REFRESH%' order by seq;

CAPABILITY_NAME                                    POS MSGTXT
-------------------------------------------------- --- --------------------------------------------------
REFRESH_COMPLETE                                   Y
REFRESH_FAST                                       Y
REFRESH_FAST_AFTER_INSERT                          Y
REFRESH_FAST_AFTER_ONETAB_DML                      N   mv uses the MIN or MAX aggregate functions
REFRESH_FAST_AFTER_ONETAB_DML                      N   COUNT(*) is not present in the select list
REFRESH_FAST_AFTER_ANY_DML                         N   see the reason why REFRESH_FAST_AFTER_ONETAB_DML i
                                                       s disabled

REFRESH_FAST_PCT                                   N   PCT is not possible on any of the detail tables in
                                                        the materialized view



 

So, the cause is COUNT(*) is not being used in MVIEW creation when aggregate functions are being used.
 
Solution
 
COUNT(*) must always be present to guarantee all types of fast refresh. Otherwise, you may be limited to fast refresh after inserts only.
COUNT(*) is needed for MV with aggregate as it records the number of rows in each group. The refresh operations may increase or decrease the count when incrementally apply the change data from log. When the count becomes 0 after applying the changes, the row in the MV should be deleted as the group no longer exists.
See the same testcase (after making changes):



 
-- Create MV with COUNT(*) as past of SELECT statement
SQL> CREATE MATERIALIZED VIEW adp_ctm_log_mv
2 BUILD IMMEDIATE
3 REFRESH FAST ON COMMIT
4 AS
5 SELECT count(*), office_bic_code, MAX(response_min_last_update_date) response_last_update_date
6 FROM adp_ctm_log
7 GROUP BY office_bic_code;

Materialized view created.

SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
-------------------
20-02-2012 11:33:19

SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'dd-mm-yyyy hh24:mi:ss') from user_mviews where mview_name = 'ADP_CTM_LOG_MV';

MVIEW_NAME TO_CHAR(LAST_REFRES
------------------------------ -------------------
ADP_CTM_LOG_MV 20-02-2012 11:33:12

SQL> INSERT INTO ADP_CTM_LOG ( LOG_PK, OFFICE_BIC_CODE, REQUEST_REF_NO, REQUEST_SENT_DATE, REQUEST_TYPE,
2 RESPONSE_MIN_LAST_UPDATE_DATE, RESPONSE_ERROR_CODE, APP_REGI_DATE, APP_UPD_DATE, CREATED_BY,
3 CREATION_DATE, UPDATED_BY, UPDATE_DATE ) VALUES (
4 17, 'TEST1', 'CRQ000000004', TO_Date( '12/28/2011 11:11:12 AM', 'MM/DD/YYYY HH:MI:SS AM')
5 , 'MultiTradeLevelRequest', TO_Date( '01/01/2012 12:07:47 PM', 'MM/DD/YYYY HH:MI:SS AM')
6 , NULL, TO_Date( '04/18/2011 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '04/18/2011 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
7 , 'SYSTEM', TO_Date( '12/28/2011 11:11:08 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'SYSTEM'
8 , TO_Date( '12/28/2011 11:11:08 AM', 'MM/DD/YYYY HH:MI:SS AM'));

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL> select count(*) from mlog$_adp_ctm_log;

COUNT(*)
----------
0

SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'dd-mm-yyyy hh24:mi:ss') from user_mviews where mview_name = 'ADP_CTM_LOG_MV';

MVIEW_NAME TO_CHAR(LAST_REFRES
------------------------------ -------------------
ADP_CTM_LOG_MV 20-02-2012 11:33:31

SQL> update adp_ctm_log set response_min_last_update_date =sysdate +3 where log_pk=17;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from ADP_CTM_LOG_MV;

COUNT(*) OFFICE_BIC_CODE RESPONSE_
---------- ---------------- ---------
1 TEST1 23-FEB-12

-- Check MV's LAST_REFRESH_DATE.

SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'dd-mm-yyyy hh24:mi:ss') from user_mviews where mview_name = 'ADP_CTM_LOG_MV';

MVIEW_NAME TO_CHAR(LAST_REFRES
------------------------------ -------------------
ADP_CTM_LOG_MV 20-02-2012 11:35:14

SQL>

-- See that it has changed; that means ON COMMIT MVIEW has refreshed successfully after the last update (and commit) statement.