Atomic refresh of MV, without the redo...

I tweeted about something that occupied me last Friday morning, and Galo Balda suggested to turn it into a blogpost:

@toonkoppelaars Blog post perhaps? :-)
— Galo Balda (@GaloBalda) May 24, 2013

Which is what *was* actually my idea when I was done with it, but instead decided to dedicate just a short tweet to it. Anyway here is the blogpost.

What was the scenario?

In order to fix performance issues with certain internal OBIEE reports, it was decided (over a year ago) to introduce a couple of materialized views. These were complex materialized views which could only be refreshed complete: no fast refresh possible. Users were requesting that these views be up-to-date every half hour. So using dbms_job a job was submitted that did a dbms_mview.refresh([mview],'C');

Refresh times of the mviews is in the order of tens of seconds. This implies that when a non atomic-refresh is used, users could see empty mviews. Remember:

• Atomic-refresh: performs a delete of all rows currently in the mview, followed by a re-execute of the underlying mview query and re-insert of all rows retrieved. And then finally a 'commit';
• Non atomic-refresh: performs a truncate (which includes an implicit commit) of the underlying mview's table segment, followed by the re-insert (and this case using +APPEND hint, btw) of rows, finally followed by a commit;

In non atomic-refresh mode, right after the truncate, users will see an empty mview for the aforementioned tens of seconds. This was not acceptable, so atomic refreshes are performed every 30 minutes. In atomic-refresh mode, users will continue to see the previous contents, up to the commit, at which time they suddenly see the new contents.

Some of the mviews held a considerable amount of (wide) rows: in the order of a few hundred thousand. Segment sizes in the tens of megabytes. In atomic-refesh mode this causes a considerable amount more of redo than is the case in non atomic-mode. The database at hand was running archive-log mode. And DBA's were planning on securing the database further by implementing a physical dataguard setup for it.

DBA's were concerned by the amount of redo generated during the day. After some investigation they found that almost all of that redo was to be attributed to the atomic-refreshes of the set of mviews.

So the challenge put forward by them was: could we please decrease that amount of redo generated?

How did we fix it?

I had recently attended Hotsos 2013. During one of Tom Kyte's presentations I was introduced with a novel way of using partition exchange to speedup full table updates. It went like this:

• Suppose you need to update a huge table, all rows. You can do that with an update statement. But that would: a) take a lot of time, b) generate a lot of undo and redo.
• Instead, assume that the table to be updated has been defined as being partitioned. In such a way that all rows would (always) go into a single partition. Right, so it is a partitioned table, but it has (and always will have) a single partition. You can do that with range partitioning on some not null column and using MAX_VALUE in the partition clause (code for this will follow later in this post).
  (I agree this is a *big* assumption, but let's assume we set up the table like this)
• What we can do now is instead of the full-table update, perform a CTAS. And in this CTAS ensure the update is reflected by employing the appropriate SELECT-clause expressions.
• Once this table has been created, we can then perform a partition exchange of this table segment with our (partitioned) table segment that was in need of the full-table update.
• Presto. Done.

The thought I had friday was: can I use this trick to fix the redo-issue with the atomic-refreshes of our mviews? So is it possible to:

1. Create a new single partition table with the same structure of the mview.
2. And then perform a non atomic-refresh on the mview.
3. And then perform a partition exchange of the mview's table segment with the partitioned table's table segment.
4. And have the users use the partitioned table, not the mview. They will see an instant update when the exchange is executed.

Turned out this works as a charm, and reduced the redo considerably (due to non atomic-refreshes used now).

So here's a quick overview of the cookbook:

Current situation:

• Mview1, currently every 30 minutes hit by an atomic-refresh.

New situation:

1. Mview1 re-created under a different name.
2. Single partition partitioned table created with original mview's name. Use some not-null column to do the range partition on.
3. Submit a dbms_job to:
• non atomically refresh the mview,
• do a partition exchange

Example code:

Ad 1) Say we recreated that mview under name mview1_tmp.

Ad 2) Creating the table with the original mview's name, with a single partition:

create table mview1
tablespace ...
partition by range([some column]) 
      -- Some *not null* column of the original MVIEW mview1.
(partition all_data values less than (MAXVALUE))
as
select *
from mview1_tmp;

Ad 3) Submit the job:

declare
pl_jobno number;
begin
  --
  dbms_job.submit(pl_jobno
                 ,'begin dbms_mview.refresh(''MVIEW1_TMP''
                                           ,''C''
                                           ,atomic_refresh=>false);
                  execute immediate ''alter table MVIEW1
                          exchange partition all_data with table MVIEW1_TMP
                         including indexes without validation'';
                   end;'
                 ,INTERVAL => 'sysdate + (30/(24*60)');
  --
end;
/

Cheers,

Toon

PS1. When switching the refresh-mode of an mview from atomic to non-atomic, one can then also get more benefits (redo-log size wise), by specifying NOLOGGING. Which is what we did too.

PS2: I was concerned whether the partition exchange would hit an ORA-00054, or something alike, when a user would still have an open cursor on the partitioned table. Turned out it didn't. A partition exchange will just successfully execute in this case.