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;
Com