Skip to Main Content
  • Questions

  • Materialized View - Complete Refresh

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Kishore.

Asked: February 05, 2016 - 5:49 am UTC

Last updated: February 08, 2016 - 6:04 am UTC

Version: 11g

Viewed 1000+ times

You Asked

and we said...

This appears to be a duplicate. We already responded to the previous copy of this with:

Can you give a cut-down test case that we can use to reproduce here, ie

- a source table
- the mat view and mat view log definition
- the partition maint operations you are doing.

Im sending required details as requested in my earlier post( as i pasted aboved).

Source Table :
----------------
ALTER TABLE SCOTT.CLM_LN
DROP PRIMARY KEY CASCADE;

DROP TABLE SCOTT.CLM_LN CASCADE CONSTRAINTS;

CREATE TABLE SCOTT.CLM_LN
(
MED_CLM_KEY VARCHAR2(32 BYTE) NOT NULL,
MED_CLM_LN_KEY VARCHAR2(32 BYTE) NOT NULL,
CLM_ID VARCHAR2(50 BYTE) NOT NULL,
CLM_SYS_CD VARCHAR2(4 BYTE) NOT NULL,
CLM_EVENT_CD VARCHAR2(8 BYTE) NOT NULL,
CLM_EVENT_TMSTP TIMESTAMP(6) NOT NULL,
CLM_LN_NUM NUMBER(10) NOT NULL,
HA_BTCH_SEQ_NUM NUMBER,
HA_BTCH_DT DATE NOT NULL,
CRET_USR_ID VARCHAR2(50 BYTE),
CRET_TMSTP TIMESTAMP(6),
LAST_UPDT_USR_ID VARCHAR2(50 BYTE),
LAST_UPDT_TMSTP TIMESTAMP(6)
)

PARTITION BY RANGE (HA_BTCH_DT)
INTERVAL( NUMTODSINTERVAL(7,'DAY'))
(
PARTITION P20090615 VALUES LESS THAN (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
NOCACHE
NOPARALLEL
MONITORING;

CREATE INDEX SCOTT.CLM_LN_IE1 ON SCOTT.CLM_LN
(HA_BTCH_DT, HA_BTCH_SEQ_NUM)
LOCAL (
PARTITION P20090615
)
NOPARALLEL;

CREATE UNIQUE INDEX SCOTT.CLM_LN_PK ON SCOTT.CLM_LN
(CLM_ID, CLM_SYS_CD, CLM_EVENT_CD, CLM_EVENT_TMSTP, CLM_LN_NUM, HA_BTCH_DT)
LOCAL (
PARTITION P20090615
)
NOPARALLEL;

ALTER TABLE SCOTT.CLM_LN ADD (
CONSTRAINT CLM_LN_PK
PRIMARY KEY
(CLM_ID, CLM_SYS_CD, CLM_EVENT_CD, CLM_EVENT_TMSTP, CLM_LN_NUM, HA_BTCH_DT)
USING INDEX LOCAL
ENABLE VALIDATE);

MV Log Script:
-----------------
DROP MATERIALIZED VIEW LOG ON SCOTT.CLM_LN;
CREATE MATERIALIZED VIEW LOG ON SCOTT.CLM_LN
NOCACHE
NOLOGGING
NOPARALLEL
WITH ROWID, PRIMARY KEY (LAST_UPDT_TMSTP)
INCLUDING NEW VALUES;

MV Script :
----------------
DROP MATERIALIZED VIEW SCOTT.CLM_LN_MV;
CREATE MATERIALIZED VIEW SCOTT.CLM_LN_MV
(
MED_CLM_KEY
,MED_CLM_LN_KEY
,CLM_ID
,CLM_SYS_CD
,CLM_EVENT_CD
,CLM_EVENT_TMSTP
,CLM_LN_NUM
,HA_BTCH_DT
,HA_BTCH_SEQ_NUM
,CRET_USR_ID
,CRET_TMSTP
,LAST_UPDT_USR_ID
,LAST_UPDT_TMSTP
)
NOLOGGING
PARTITION BY RANGE (LAST_UPDT_TMSTP)
INTERVAL( NUMTODSINTERVAL(1,'DAY'))
(
PARTITION P20151101 VALUES LESS THAN (TIMESTAMP' 2015-11-01 00:00:00')
)
NOCACHE
NOCOMPRESS
NOPARALLEL
BUILD DEFERRED
USING INDEX
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS
SELECT MED_CLM_KEY
,MED_CLM_LN_KEY
,CLM_ID
,CLM_SYS_CD
,CLM_EVENT_CD
,CLM_EVENT_TMSTP
,CLM_LN_NUM
,HA_BTCH_DT
,HA_BTCH_SEQ_NUM
,CRET_USR_ID
,CRET_TMSTP
,LAST_UPDT_USR_ID
,LAST_UPDT_TMSTP
FROM SCOTT.CLM_LN
WHERE TRUNC(LAST_UPDT_TMSTP) >= SYSDATE@! - 7;

CREATE UNIQUE INDEX SCOTT.CLM_LN_PK1 ON SCOTT.CLM_LN_MV
(CLM_ID, CLM_SYS_CD, CLM_EVENT_CD, CLM_EVENT_TMSTP, CLM_LN_NUM,
HA_BTCH_DT,LAST_UPDT_TMSTP)
LOGGING
LOCAL
(
PARTITION P20090615
)
NOPARALLEL;

Alter Script:
--------------
ALTER MATERIALIZED VIEW CLM_LN_MV DROP PARTITION P20090615;

MV Refresh Process:
-------------------
EXEC DBMS_MVIEW.REFRESH('SCOTT.CLM_LN_MV','?');

Thanks

and Connor said...

I can't even get to the point you are referring to, because dropping partitions out of a materialized view gets an error - do you not see the same error ?


SQL> CREATE TABLE SCOTT.CLM_LN
  2  (
  3  MED_CLM_KEY VARCHAR2(32 BYTE) NOT NULL,
  4  MED_CLM_LN_KEY VARCHAR2(32 BYTE) NOT NULL,
  5  CLM_ID VARCHAR2(50 BYTE) NOT NULL,
  6  CLM_SYS_CD VARCHAR2(4 BYTE) NOT NULL,
  7  CLM_EVENT_CD VARCHAR2(8 BYTE) NOT NULL,
  8  CLM_EVENT_TMSTP TIMESTAMP(6) NOT NULL,
  9  CLM_LN_NUM NUMBER(10) NOT NULL,
 10  HA_BTCH_SEQ_NUM NUMBER,
 11  HA_BTCH_DT DATE NOT NULL,
 12  CRET_USR_ID VARCHAR2(50 BYTE),
 13  CRET_TMSTP TIMESTAMP(6),
 14  LAST_UPDT_USR_ID VARCHAR2(50 BYTE),
 15  LAST_UPDT_TMSTP TIMESTAMP(6)
 16  )
 17  PARTITION BY RANGE (HA_BTCH_DT)
 18  INTERVAL( NUMTODSINTERVAL(7,'DAY'))
 19  (
 20  PARTITION p1 VALUES LESS THAN (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 21  )
 22  NOCACHE
 23  NOPARALLEL
 24  MONITORING;

Table created.

SQL>
SQL> insert into SCOTT.CLM_LN
  2  select
  3   rownum MED_CLM_KEY
  4  ,rownum MED_CLM_LN_KEY
  5  ,rownum CLM_ID
  6  ,mod(rownum,400) CLM_SYS_CD
  7  ,rownum CLM_EVENT_CD
  8  ,sysdate - 50 + rownum/1000 CLM_EVENT_TMSTP
  9  ,rownum CLM_LN_NUM
 10  ,rownum HA_BTCH_SEQ_NUM
 11  ,sysdate - 50 + rownum/2000 HA_BTCH_DT
 12  ,rownum CRET_USR_ID
 13  ,sysdate  - 50 + rownum/2000 CRET_TMSTP
 14  ,rownum LAST_UPDT_USR_ID
 15  ,sysdate - 50  + rownum/2000 LAST_UPDT_TMSTP
 16  from dual
 17  connect by level <= 100000;

100000 rows created.

SQL>
SQL>
SQL> CREATE INDEX SCOTT.CLM_LN_IE1 ON SCOTT.CLM_LN
  2  (HA_BTCH_DT, HA_BTCH_SEQ_NUM)
  3  LOCAL
  4  NOPARALLEL;

Index created.

SQL>
SQL>
SQL> CREATE UNIQUE INDEX SCOTT.CLM_LN_PK ON SCOTT.CLM_LN
  2  (CLM_ID, CLM_SYS_CD, CLM_EVENT_CD, CLM_EVENT_TMSTP, CLM_LN_NUM, HA_BTCH_DT)
  3  LOCAL NOPARALLEL;

Index created.

SQL>
SQL>
SQL>
SQL> ALTER TABLE SCOTT.CLM_LN ADD (
  2  CONSTRAINT CLM_LN_PK
  3  PRIMARY KEY
  4  (CLM_ID, CLM_SYS_CD, CLM_EVENT_CD, CLM_EVENT_TMSTP, CLM_LN_NUM, HA_BTCH_DT)
  5  USING INDEX LOCAL
  6  ENABLE VALIDATE);

Table altered.

SQL> CREATE MATERIALIZED VIEW LOG ON SCOTT.CLM_LN
  2  NOCACHE
  3  NOLOGGING
  4  NOPARALLEL
  5  WITH ROWID, PRIMARY KEY (LAST_UPDT_TMSTP)
  6  INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW SCOTT.CLM_LN_MV
  2  (
  3  MED_CLM_KEY
  4  ,MED_CLM_LN_KEY
  5  ,CLM_ID
  6  ,CLM_SYS_CD
  7  ,CLM_EVENT_CD
  8  ,CLM_EVENT_TMSTP
  9  ,CLM_LN_NUM
 10  ,HA_BTCH_DT
 11  ,HA_BTCH_SEQ_NUM
 12  ,CRET_USR_ID
 13  ,CRET_TMSTP
 14  ,LAST_UPDT_USR_ID
 15  ,LAST_UPDT_TMSTP
 16  )
 17  NOLOGGING
 18  PARTITION BY RANGE (LAST_UPDT_TMSTP)
 19  INTERVAL( NUMTODSINTERVAL(1,'DAY'))
 20  (
 21  PARTITION p1 VALUES LESS THAN (TIMESTAMP' 2015-11-01 00:00:00')
 22  )
 23  NOCACHE
 24  NOCOMPRESS
 25  NOPARALLEL
 26  BUILD DEFERRED
 27  USING INDEX
 28  REFRESH FAST ON DEMAND
 29  WITH PRIMARY KEY
 30  AS
 31  SELECT MED_CLM_KEY
 32  ,MED_CLM_LN_KEY
 33  ,CLM_ID
 34  ,CLM_SYS_CD
 35  ,CLM_EVENT_CD
 36  ,CLM_EVENT_TMSTP
 37  ,CLM_LN_NUM
 38  ,HA_BTCH_DT
 39  ,HA_BTCH_SEQ_NUM
 40  ,CRET_USR_ID
 41  ,CRET_TMSTP
 42  ,LAST_UPDT_USR_ID
 43  ,LAST_UPDT_TMSTP
 44  FROM SCOTT.CLM_LN
 45  WHERE TRUNC(LAST_UPDT_TMSTP) >= SYSDATE - 7
 46  ;

Materialized view created.

SQL>
SQL> EXEC DBMS_MVIEW.REFRESH('SCOTT.CLM_LN_MV','?');

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> CREATE UNIQUE INDEX SCOTT.CLM_LN_PK1 ON SCOTT.CLM_LN_MV
  2  (CLM_ID, CLM_SYS_CD, CLM_EVENT_CD, CLM_EVENT_TMSTP, CLM_LN_NUM,
  3  HA_BTCH_DT,LAST_UPDT_TMSTP)
  4  LOGGING
  5  LOCAL
  6  NOPARALLEL;

Index created.

SQL>
SQL> col partition_name format a30
SQL> col high_value format a60
SQL>
SQL> select partition_name , high_value from all_tab_partitions
  2  where table_name = 'CLM_LN_MV'
  3  order by partition_position;

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------
P1                             TIMESTAMP' 2015-11-01 00:00:00'
SYS_P2495                      TIMESTAMP' 2016-02-01 00:00:00'
SYS_P2492                      TIMESTAMP' 2016-02-02 00:00:00'
SYS_P2493                      TIMESTAMP' 2016-02-03 00:00:00'
SYS_P2494                      TIMESTAMP' 2016-02-04 00:00:00'
SYS_P2496                      TIMESTAMP' 2016-02-05 00:00:00'
SYS_P2497                      TIMESTAMP' 2016-02-06 00:00:00'
SYS_P2498                      TIMESTAMP' 2016-02-07 00:00:00'

8 rows selected.

SQL>
SQL>
SQL> ALTER MATERIALIZED VIEW CLM_LN_MV DROP PARTITION SYS_P2463;
ALTER MATERIALIZED VIEW CLM_LN_MV DROP PARTITION SYS_P2463
                                       *
ERROR at line 1:
ORA-32339: cannot alter materialized view with the PMOP


SQL>


I'm marking this "Answered" just so you can easily add things via Reviews, and we'll keep working on it as we go.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you.

Kishore Yeddala, February 06, 2016 - 1:33 pm UTC

Hi, if the data inserted falls within Sysdate - 7 day range I.e the filter condition on mv definition.. I believe we can't drop a partition. Otherwise we were able to drop partition without any issue.
Connor McDonald
February 08, 2016 - 6:04 am UTC

OK, the only way I can replicate what you are saying is by going directly to the table:

SQL> ALTER MATERIALIZED VIEW CLM_LN_MV DROP PARTITION SYS_P2655 update indexes;
ALTER MATERIALIZED VIEW CLM_LN_MV DROP PARTITION SYS_P2655 update indexes
                                       *
ERROR at line 1:
ORA-32339: cannot alter materialized view with the PMOP

SQL> alter table scott.CLM_LN_MV drop partition SYS_P2624;

Table altered.


and that's a definite no-no.

But think about it for a minute.

In order for "create materialized view mv refresh fast as select * from t where date_column > sysdate-7" to work - we'd have to put a "delete" record into the materialized view log every time sysdate advanced by one second and a new record(s) was to be deleted.

If the materialized view log were to be used, we would need to know

a) NEW records
b) MODIFIED records
(so far, so good)

c) records to be deleted from the mview.... we can get the records that were really deleted, but how would you suggest to record all of the records that need to be deleted based on the "date_column > sysdate-7" constraint? Because they are *not* in the mview log, because nothing has *really* deleted them from the original table.

I'm suprised the drop partition actually is allowed at all to be honest, but certainly not surprised you then require a complete refresh, because the materialized view has been "diddled" behind the scenes.

Similarly, the materialized view does not *accurately* reflect the 7 day date range until you do the complete refresh (even if you do not drop the partitions).

You really need to do complete refresh each time.

Fallowup

Kishore Yeddala, February 06, 2016 - 1:53 pm UTC

I was just checking the drop statement u have on the reply, the partition name I see on the drop is not listed from all partition query. Not sure whether that's causing an error.
Connor McDonald
February 07, 2016 - 4:17 am UTC

Sorry - that's just a cut/paste fail on my part. You get the same with a valid partition name.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.