现象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_AD