Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Hrvoje.

Asked: July 19, 2017 - 9:44 am UTC

Last updated: October 09, 2017 - 4:52 pm UTC

Version: Oracle DB 12.1.0.2

Viewed 1000+ times

You Asked

Hello Tom !

A question about PMOs on MVIEWs.

It seems that PMO (split, merge, add, drop, rename, ... partition) operations on materialized views are working without need to drop materialized view to its prebuilt table.
Also for FAST refreshable mviews SPLIT and MERGE PARTITION operations (as we found by trying out) do not influence their mview logs,
neither local nor on remote server (used for replication).
Only the DROP PARTITION puts materialized view in the state which must be CONSIDERED FRESH and consequently mview log will be purged.
Please confirm it, as I can't find anything about it in the documentation.
Also, are some locks imposed on mview logs on remote DB when SPLIT and MERGE operations are handled?

and Chris said...

Well, in general you can't drop a partition from a materialized view!

create table t ( x primary key, y not null ) as
  select rownum x, rownum y from dual connect by level <= 1000;
  
create materialized view mv 
  partition by range (x) (
    partition p0 values less than (501),
    partition p1 values less than (1001),
    partition pmax values less than (maxvalue)
  ) 
  as 
  select * from t;

alter materialized view mv drop partition p0;

ORA-32339: cannot alter materialized view with the PMOP


By doing this you're removing data from the MV. So it's no longer in sync with the base table. So even if it was allowed the mview log would be irrelevant. You need to do a complete refresh to ensure that it's back in sync with the underlying table(s).

Rating

  (3 ratings)

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

Comments

PMOs on MVIEW

Hrvoje Devcic, July 20, 2017 - 3:59 pm UTC

Hello !

Thank you!

I'm aware of that.
But my original question is more leaned on SPLIT and MERGE PARTITION operations on MVIEWs.
As there is nothing stated in the documentation about it and we succeeded to execute them (even DROP PARTITION) on mview with set REFRESH FAST ON DEMAND on Oracle 12.1.0.2, I'm little worried now because of the exception you've written.

The problem arises because the DROP and CREATE MATERIALIZED VIEW statements PURGE automatically its MVIEW LOG and we need to avoid that as we can't pause working of the master site.
So my request was if you could confirm that we can SPLIT and MERGE mview partitions safely without dropping mview.
And also if it so, are there some locks imposed on the mview log while these operations are handled.

Chris Saxon
July 20, 2017 - 4:30 pm UTC

Yeah, you can split/merge partitions in an MV. And any outstanding changes will remain in the mv log:

create table t ( x primary key, y not null ) as
  select rownum x, rownum y from dual connect by level <= 1000;

create materialized view log on t;  
create materialized view mv 
  partition by range (x) (
    partition p0 values less than (501),
    partition p1 values less than (1001),
    partition pmax values less than (maxvalue)
  ) 
  refresh fast on demand as 
  select * from t;
  
insert into t values (0, 0);
commit;

select x from mlog$_t;

         X
----------
         0

select count(*) from mv partition (p1) ;

  COUNT(*)
----------
       500

alter table mv merge partitions p0, p1 
  into partition p1 update global indexes;

select count(*) from mv partition (p1) ;

  COUNT(*)
----------
      1000

alter table mv split partition p1 at (100) 
  into (partition p0, partition p1) update global indexes;

select count(*) from mv partition (p1) ;

  COUNT(*)
----------
       901

select x from mlog$_t;

         X
----------
         0

exec dbms_mview.refresh('mv', 'F');

select * from mv where x = 0;

         X          Y
---------- ----------
         0          0


"are there some locks imposed on the mview log while these operations are handled."

What exactly is your concern here?

Hrvoje Devcic, July 20, 2017 - 6:12 pm UTC

Great !

That I wanted to hear :-)
I hope this behavior will not change as it is not explicitely documented.

My worries about locks are whether SPLIT/MERGE PARTITION operations on mview can pose TM lock on its mview log and consequently similar lock on its master table.
As master site is not our system - three hours MERGE operation with locking master table for DMLs would be forbidden for us. (Mview log is on remote database).
I'm asking this because for example ALTER MATERIALIZED VIEW LOG ON <some_table> PURGE NEXT SYSDATE + 3 also locks <some_table>. Shortly, for few seconds, but it is not allowed even that.
Chris Saxon
October 09, 2017 - 4:52 pm UTC

MOS notes 258252.1 & 258258.1 have detailed discussions of the locks taken out during mview refreshes.

Hrvoje Devcic, July 27, 2017 - 1:20 pm UTC

Hello !

Please don't forget to answer my question about locks on mview log when PMOs are conducted on mview.

And it would be nice if documentation is updated with explanation that SPLIT and MERGE PARTITION operations can be done on mview without dropping it.

Thank you !

BR
Hrvoje

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.