Sunday, April 04, 2010

Online Materialized View Complete Refresh With Partitions

The other day I was posed with an interesting problem regarding the refresh of the materialized views. First, a little background. MVs are nothing new in Oracle; they have been around since Oracle 7 (called snapshots then). Essentially they are similar to tables (as they actually store data) but populated by running a query on some source tables. This query is the defining query of the MV. So, the user can select from the MV instead of executing the defining query – reducing the execution time significantly.

However, the MV and the query would both give the same result if the source tables have not changed. If the data has changed, the MV will not have known about it and will give a stale result. Therefore, you need to refresh the MV from time to time. Usually the MVs are refreshed by the following method:

begin
dbms_mview.refresh(‘’);
end;


So, what’s the problem? The problem is, during complete refreshes, the MV remains unavailable to the users. This duration of refresh depends on the size of the MV, the execution time of the underlying query and so on; and the unavailability is a nuisance for most applications. In some cases the applications are designed to timeout after some wait; and they do so quite consistently in this case – making it more than just a nuisance.

So, I was asked to devise a solution to make the MVs available during the complete refreshes. I accomplished that with a not-so-common use of partitions. Here is how I did it.

Prebuilt Table

First, as an architectural standard, I advocate the use of prebuilt tables in MVs. Here is how an MV is usually created:

create materialized view mv_bookings_01
refresh complete on demand
enable query rewrite
as
select hotel_id, sum(book_txn) tot_txn
from bookings
group by hotel_id
/

This creates the segment mv_bookings_01 during this process. Instead of this approach, I recommend first creating and populating a regular table and then converting that to an MV. In this revised approach, you would first create the table:

create table mv_bookings_01
as
select HOTEL_ID, sum(book_txn) tot_txn
from bookings
group by HOTEL_ID
/


Then, you would create the MV using the prebuilt table option:

create materialized view mv_bookings_01
on prebuilt table
refresh complete on demand
enable query rewrite
as
select HOTEL_ID, sum(book_txn) tot_txn
from bookings
group by HOTEL_ID
/

This does not do anything to the functionality of the MV itself. The MV feels, looks and smells like it was before; but with two significant advantages:

(1) You can easily alter the columns
(2) You can manipulate the table

I blogged about it at http://arup.blogspot.com/2009/01/ultra-fast-mv-alteration-using-prebuilt.html. Anyway, back to the issue at hand. Using the prebuilt trick, I can create a partitioned MV as well. In this specific case, I will add a column to partition on. Remember, this column does not actually participate in the application; it simply creates an anchor point for the partition.

create table mv_bookings_01
(
ref_type number,
hotel_id number(3),
tot_txn number
)
partition by list (ref_type)
(
partition p0 values (0),
partition p1 values (1)
)
/

After the table is created, I can insert data into it:

insert /*+ append */ into mv_bookings_01
select 0, hotel_id, sum(book_txn) tot_txn
from bookings
group by 0, hotel_id
/

Of course I could have selected the data from the source tables directly while creating the prebuilt table; but I just wanted to show a different approach of data loading. The column REF_TYPE is not part of the application; but I have added it to divide the table along a value by list partitioning. The column can hold only two values – 0 and 1, and hence the table has only two partitions.

Once the table is created, I can use the prebuilt table option to create the MV as shown above. However, the presence of the new column makes it a little different. My MV creation script now looks like this:

create materialized view mv_bookings_01
on prebuilt table
enable query rewrite
as
select 0 as ref_type, HOTEL_ID, sum(book_txn) tot_txn
from bookings
group by 0, HOTEL_ID
/

Note that I have used “0 as ref_type” in the select clause, i.e. the ref_type will always be 0. This is not going to a problem as you can see later. When the MV is first created, the value of ref_type is 0; hence the partition p0 is the one is that is populated; not the partition p1. We can confirm that:

SQL> select count(1) from mv_bookings_01 partition (p0);

COUNT(1)
----------
2

SQL> select count(1) from mv_bookings_01 partition (p1);

COUNT(1)
----------
0

Now that the partitions are in place, let’s see how we refresh this MV. We will no longer use the dbms_mview approach. Rather, we will use this:

(1) Create a temporary table
(2) Create all the indexes
(3) Exchange the partition that is not used with this temporary table

Step 1: Temporary table

First we create a temporary table that is identical to the prebuilt table of the MV in structure; but just not partitioned. We will use the value of ref_type column as 0 or 1 based on the value already in the table, in fact exactly opposite of what is in the table. Since we have 0 in the table, we will use 1 in the temporary table. This temporary table will contain the data that we need refreshed as.

create table tmp1
as
select 1 as ref_type, hotel_id, sum(book_txn) tot_txn
from bookings
group by 1, hotel_id
/

We also need to create another temporary table with no data.

create table tmp0
(
ref_type number,
hotel_id number(3),
tot_txn number
)
/

Step 2: Create Indexes

In this case we don’t have any indexes on the MV; but if we had, we would have created them here on the TMP0 and TMP1 tables.

Step 3: Exchange Partitions

When the temporary table is ready, we can exchange the inactive partition with this.

alter table mv_bookings_01
exchange partition p1
with table tmp1
including indexes
without validation
update global indexes
/
alter table mv_bookings_01
exchange partition p0
with table tmp0
including indexes
without validation
update global indexes
/

This SQL performs a switch: the segment that was called partition P1 in the table mv_bookings_01 is now called TMP1 and former segment TMP1 is now called partition P1 in the table mv_bookings_01. This occurs at the data dictionary level; no actual data is ever transferred from one segment to the other. As a result this is very quick. A lock is required on the table only for that instance is such an insignificant amount of time that it may not be even noticeable. Similarly the segment used to be called P0 partition is now known as TMP0 table and TMP0 is called P0.

There are several key things to note here. Note the clause “without validation”, which tells Oracle not to bother checking inside the TMP0 table that it will confirm to the partition specification. This is critical for the performance. Since we built the table with “1” as a hardcoded value, we know that the REF_TYPE column will definitely contain 1, satisfying the partition requirement for P1. A further checking is not required.

The “including indexes” clause switched the local index partitions as well. If there is a global index on the MV’s prebuilt table, that would have been invalidated and needed rebuilding afterwards. But, we avoided that invalidation by using a “update global indexes” clause in the alter statement.

Once the switchover is complete, we can check the MV to see if the data has been visible.

SQL> select count(1) from mv_bookings_01 partition (p0)
2 /

COUNT(1)
----------
0

SQL> select count(1) from mv_bookings_01 partition (p1)
2 /

COUNT(1)
----------
2

This completes the refreshing of the MV and the data is visible to end users. Let’s see the timeline

(1) Building of temporary table
(2) Building of indexes on the temporary table
(3) Exchange partitions

Steps 1 and 2 take most of the time; but that is done offline, without affecting the MV itself. So, long time there does not affect the availability of the MV itself. The step 3 is where the availability is impacted; but that is miniscule.

The table TMP0 now has the rows from partition P0 of the MV. To confirm that:

SQL> select count(1) from tmp0;

COUNT(1)
----------
2

You can drop the table by “drop table tmp0 purge”. You should also drop TMP1 since that table will contain the previous contents of the partition P1, which is useless now.

The next time this MV needs refreshing, you have to repeat the process; but with a very important distinction – the partition P0 needs exchange now. I used a script that checks the value of REF_TYPE in the MV now and use the other value passed to an SQL script with a positional parameter that accepts 0 or 1. Using this approach you can refresh a MV very quickly.

Oracle 11g Variation

In Oracle 11g, there is a new feature – Virtual Column. You can define a column as virtual in the table, which is not actually stored in the table; but calculated during runtime. What’s more, this virtual column can also be used as a partition key. You can define the REF_TYPE as a virtual column in that case.

Takeaways

(1) Just because there is an Materialized View, it does not have to be refreshed by the usual approach, i.e. dbms_mview.refresh. There are other options as well.
(2) Partitioning is not just for performance or purge reasons; it can be used in clever ways such as MV refreshes.
(3) In this specific case, we didn’t have a column that could have been used as a partitioning key; so we had to resort to adding a column. In many cases, the table may already have a column for that purpose, making the process even simpler.

10 comments:

Ilya said...

Hi.

I'm sorry, but I think your approach is somewhat flawed as the query made between the two 'exchange partitions' would return both the old and the newer data.

I had to implement similar approach some time ago. I've used prebuilt table with just a single partition and the second MV with query rewrite disabled to serve as one of yours temporary tables for simplier refresh process (with atomic_refresh=false for truncate). Since just a single exchange is required, the operation is atomic and should not lead to wrong results. But that was only implemented on development system and never gone live. So I may be missing something that would prevent using a single partition approach, and I'd like to hear your comments on why did you use two partitions please.

Btw, you mention that mview is unavailable to the users during the complete refresh. By that do you mean that the data is not available during the atomic_refresh=false refresh due to truncate or something else?

Arup said...

@Ilya:

Thanks for the feedback.

Yes, that would be the case. Actually my set up was a little more complicated than this simpler setup I outlined. The two partitions were necessary then and I developed this example along those lines. But I agree, there is no need to have 2 partitions in this particular case.

>> But that was only implemented on development system and never gone live.

Why was not put in production? Did you encounter some issue?

>> unavailable to the users during the complete refresh.

Actually, by default the MV refresh does not even do truncate anymore (at least in 11g where I am doing a test right now). It does a delete and then a regular insert (not truncate and not a direct path insert), so it is purely transactional in nature. Here is an excerpt from a 10046 trace:

/* MV_REFRESH (DEL) */ delete from "ARUP"."MV_BOOKINGS_01"

/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "ARUP"."MV_BOOKINGS_01"("HOTEL_ID","TOT_TXN") SELECT "BOOKINGS"."HOTEL_ID",SUM("BOOKINGS"."BOOK_TXN") FROM "BOOKINGS" "BOOKINGS" GROUP BY "BOOKINGS"."HOTEL_ID"

The problem is delete and regular inserts have to follow read consitency. The SELECTs that started earlier will see the pre-delete data, constructed from undo segments. In some cases, the selects may fail with ORA-1555 Snapshot Too Old. But in all cases, the queries will take a lot longer. This may lead to timeouts in some application servers - leading to an artificial unavailability.


About the two partitions, I think I will writr another blog rather than modifying this one. But I would be highly interested in learning why yours never made to production.

Ilya said...
This comment has been removed by the author.
Ilya said...

>> Why was not put in production? Did you encounter some issue?

No, we just dropped the code requireing that functionality as it was obsoleted by newer part of application.

>> Actually, by default the MV refresh does not even do truncate anymore (at least in 11g where I am doing a test right now).

As I understand it is controlled by 'atomic_refresh' argument to dbms_mview.refresh (default is true as stated in documentation). Oracle8i Data Warehousing Guide mentions it in "Tips for Refreshing Using Refresh" and Database Data Warehousing Guide 10g states the same.

iudith said...

Hello Arup,

First of all, congratulations for your excellent presentation style both in this blog as in the many other places where I enjoy your white papers and articles for such a long time by now !!!

I have a question related directly to this issue and the previous comments:

In Oracle10g we were often using
DBMS_MVIEW.REFRESH('mview','C',atomic_refresh=>false);
for refreshing a single usually big materialized view.

Maybe I should add here that we speak about mviews used for data replication between databases, not as a DWH tool.

In Oracle10g this worked very nicely and it was indeed much faster than relying on the default
ATOMIC_REFRESH => TRUE,
that is, it performed indeed a TRUNCATE + INSERT/*+ APPEND */.

My question is:
Has this behavior changed in Oracle11gR1 ?

I ask because today I experienced the following:
1. I tried to perform such a refresh with ATOMIC_REFRESH=>FALSE
against a remote site, to which network connections are not very fast.
After some time, the connection
"died" and my SQL*PLUS session was not seen any more in the database.

2. In a new session, I checked the
materialized view table and it showed COUNT(*) = 0,
which would suggest that it DID PERFORM A TRUNCATE and NOT a DELETE.
However, performing the SELECT COUNT(*) on the empty table
took a very long time, which would rather suggest that a TRUNCATE was however not performed, or, anyway, not with the default of DROP STORAGE.
Only after performing an explicit TRUNCATE TABLE did the SELECT COUNT(*) become fast.


3. After the manual TRUNCATE,
I tried to perform again the
DBMS_MVIEW.REFRESH(...) with the
ATOMIC_REFRESH=>FALSE,
but this time as a database job submitted through DBMS_JOB, to avoid the network disconnections of my SQL*PLUS session.
The refresh job seems to be still working, though it disappeared from
the V$SESSION_LONGOPS after having been there for a while.


It would be great whether you could
confirm or infirm the conclusion of the last commentor Ilya, regarding Oracle11g NOT perfoming any more a TRUNCATE, even with ATOMIC_REFRESH=>FALSE.

If this is true, then is the MANUAL(or explicit) TRUNCATE the ONLY way to emulate the previous version behavior ?

Thanks a lot & Best Regards,
Iudith Mentzel
ZIM Integrated Shipping Services Ltd,
Haifa, Israel

Arup said...

@Iudith: thank you for your kind compliments.

About the behavior change, yes, in 11g it has changed. However, this is something I learned over period of time working on the issue; it is probably not documented anywhere.

About "If this is true, then is the MANUAL(or explicit) TRUNCATE the ONLY way to emulate the previous version behavior ?", that seems to be the case. But whether or not it is not true, would still use it as I explained it in the artcle - doing a exchange partition because it is so much superior. The only problem I can think of is the need to have partitioning option.

Ankita said...

Hi Arup,

I'm a learner of MV concepts and liked your blogs on the same. I have a different question, not directly linked to this specific topic and my apologies if this is not the correct place to post this question.

Question:
If the MV is created using SQL that involves joining few tables and one view, will it still work in FAST REFRESH? We are going to create such MV with view log on each underlying table (including the ones used by the view). One of this is not having PK, so had to create the view log on ROWID. Not sure if that could be one reason for its bad performance, testing is still going on.

Thanks,
Ankita

Anonymous said...

Hi Arup,

I have followed the same steps as you mentioned to create the MV. But got the error while exchanging partition.

Uptill step 2 i.e creating Index on tmp table eveything goes well.

When tried ALTER TABLE DETAIL_DEVICE_MV EXCHANGE PARTITION p1 WITH TABLE tmp1 INCLUDING INDEXES WITHOUT VALIDATION UPDATE GLOBAL INDEXES

ORA-01732 data manipulation operation is not legal on this view.

Could you please elaborate how to refresh the MV next time after it is created.


I'm using oracle 11g R2 SE.
Many thanks
-Alex.

Corey Iqbal said...

Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon.

Anonymous said...

can an simple updatable materialised view push data to table ?
desc emp
(empcd number,
empname varchar2(20);
--created below emp_mv@remotedatabase

eg. insert into emp_mv values (111,'test')

can we insert this way in a materialized view...

Translate