Saturday, January 24, 2009

Ultra-Fast MV Alteration using Prebuilt Table Option

Here is an interesting question posed to me one time and I had found a solution. After 9 years, I encountered the same question and was shocked to find that many people still don't know about a little trick that could avoid a potential problem later.

Someone asked me how to modify a column of a Materialized View, e.g. from varchar2(20) to varchar2(25), or something similar. Drop and recreate? Not an option. We are talking about a several hundred GB MV with a very complex query that will take days to complete.

When you alter a materialized view to add a column or modify a column definition, unfortunately there is no command functionally equivalent to ALTER MATERIALIZED VIEW … ADD COLUMN. The only way to alter an MV is to completely drop and recreate it with the alteration. That approach may be acceptable for small MVs; but for larger MVs the cost of rebuilding can make the process quite infeasible. In addition to the time it will take to rebuild the entire MV (which could be days, depending on the size), the redo/undo generation and the surge in logical I/O due to the MV query may seriously affect the performance of the source database. In some cases, large MVs may even fail to be rebuilt as sometimes the undo segments may not have the undo information for long running queries – causing ORA-1555 errors.

So is there a better approach? Yes, there is. In this document I am going to explain a better approach for creating an MV that makes the alterations possible without rebuilding the MV – a task accomplished in mere seconds as opposed to potentially days.

Concept of Segments

Segments are stored units in Oracle. So, a table has a segment; not a view – since the contents of the view are not stored; only the view definition is. A Materialized View, however, stores the contents; so it is a segment.

Actually, the concept of segment goes a little bit further. If the table is partitioned, then each partition is a different segment. So, the relationship between tables and segments is one-to-many.

When you create an object that needs storage, such as a table, an MV or an index, Oracle first creates the corresponding segment. Once that is complete, the segment is shrouded by the cover of the object. The segment still continue to exist; but is now connected to the object. Until the segment is completely created and populated, the object technically does not exist. The segment may, in some cases, have a different name from the object. If the segment creation (or population) fails, Oracle automatically cleans up the remnants of the failed segment; but sometimes it may not be, leaving behind the chards that are eventually cleaned up by SMON process.

MVs and Segments

Anyway, how is this discussion about segments relevant to our objective here –the fast alteration of MViews?

Plenty. Remember, MVs are nothing but tables behind the covers? Property-wise, MVs and tables are like sisters, not even cousins. You can think of MVs are regular tables with some built in intelligence about how they were created (the defining query), how often they should be refreshed automatically by a job and how queries should be transformed to take advantage of the presence of the MVs. But apart from that, there is not much difference. You can directly insert into an MV, create indexes and so on. As far as a segment is concerned, there is no difference between an MV and a table. In fact Oracle stores the segment as a table:

2 from user_segments
3 where SEGMENT_NAME = 'MV1';


However, the biggest difference is the very issue we are discussing – you can’t add/modify columns of an MV while you can do that freely for a table. If I could attempt to logically represent tables and MVs, here is how it would look like.

The segment is the same. If it was created as an MV, the properties of MV take over the segment. If it was created as a table, the properties of a table take over the control.

Prebuilt Table

Since under the covers the segment is the same for both MV and table, can’t you take advantage of the fact? Suppose you have a table and you now want to convert that to an MV. In other words, you want to repoint that arrow initially pointed at the table to the MV properties:

Can you do it? Yes, of course you can. Since at the segment level it is the same, Oracle allows you to do it. When you create an MV, you can use a special clause ON PREBUILT TABLE. Here is how you create a MV in the regular approach:

create materialized view mv1
never refresh as
select cast(count (1) as number(10)) cnt from t1;

If you check the objects created:

SQL> select object_id, data_object_id, object_type
2 from user_objects
3 where object_name = 'MV1';

---------- -------------- -------------------
74842 74842 TABLE

So, it creates two objects – a table and an MV - anyway. Note a very important difference though: the DATA_OBJECT_ID for the MV object is null. If you drop the MV and check for the objects:

SQL> drop materialized view mv1;

Materialized View dropped.

SQL> select object_id, data_object_id, object_type
2 from user_objects
3 where object_name = 'MV1';

no rows selected

Even though there were two objects – a table and an MV, when you dropped the MV, both were dropped. The table object didn’t have an independent existence. Dropping the MV drops the table automatically.

Now, in the modified approach, you first create the table in the same name as the MV you are going to create:

SQL> create table mv1 (cnt number(10));

Next you create the MV by adding a new clause called ON PREBUILT TABLE shown below:

create materialized view mv1
on prebuilt table
never refresh
select cast(count (1) as number(10)) cnt from t1;

Now there will be two objects as well – one table and one MV. The MV simply took over the command over the segment but since the table already existed, it did not recreate the table object. So there are still only 2 objects.

One concern: since you created the table manually, can you accidentally drop it? Let’s see:

SQL> drop table mv1;
drop table mv1
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "ARUP"."MV1"

That answers it. The table simply loses its independent existence. However, see what happens when you drop the MV:


Materialized view dropped.

Now check the segment:

SQL> select segment_type
2 from user_segments
3 where segment_name = 'MV1';


The segment still exists! When you dropped the MV, the segment was not dropped; it simply reverted to being a table. You can confirm that by checking the objects view:

---------- -------------- -------------------
77432 77432 TABLE

Voila! The object still exists as a table. Previously you saw dropping the MV removed all the objects and the segment. However, in this approach the segment was preserved. Since it reverted to a table, you can do all things possible in a table – select from it, create index, and - most important – modify the column. You can alter the column to make NUMBER(11).

SQL> alter table mv1 modify (cnt number(11));

Table altered.

Now, create the MV again:

create materialized view mv1
on prebuilt table
never refresh as
select cast(count (1) as number(11)) cnt from t1;

That’s it. The MV is altered. The whole process took about a few seconds, and since you didn’t have to recreate the segment, you saved enormous load on the database. Here a schematic representation of what happened.

Now you know how powerful prebuilt table option is. It only affects how you define the MV; nothing else. All other properties of the MV remain intact. The end users don’t even know about the prebuilt table option; but for the DBA it remains a powerful tool in the arsenal. As a best practice I recommend creating any MV, regardless of size, with the ON PREBUILT TABLE clause. In small tables you probably don’t see a huge advantage; but what if today’s small table grows to a large one tomorrow? It’s better to be safe than sorry.

Conversion to the New Approach

Now that you understand the power of the prebuilt option, you may be wondering how to convert the existing MVs to the new clause. Unfortunately there is no conversion path. You have to drop and recreate the MVs. That is why this time – when we are moving MVs to new tablespaces – we have the golden opportunity.

One approach is to create new tables with new names and then rename them. Here are the steps:

1. Create a table with nologging clause from the old MV
create table new_mv1
select * from mv1;

2. Capture the MV definition from the data dictionary:

select dbms_metadata.get_ddl ('MATERIALIZED_VIEW','MV1')
from dual ;

… and so on …

3. Spool this to a file to be executed later.

4. Edit this file to place ON PREBUILT TABLE CLAUSE.


5. Take a Data Pump export with CONTENTS=METADATA_ONLY option. This creates all relevant privileges on the export dump file. Keep it aside.

6. Drop the Materialized View MV1.

7. Rename table NEW_MV1 to MV1

8. Execute the script you created earlier to recreate the MV.

9. Import the export dump file. It will recreate all the privileges.

This is slow; but the best approach since it generates minimum amount of redo and undo.

Hope this is helpful. You may look at an article I wrote The article describes, with complete code, how to alter an MV where the refresh occurs across databases.


Anonymous said...

Excellent article Arup. Thanks and this will be very helpful.

Anonymous said...

Hi Arup -

This article was really helpful for me.

I have been a reader of all your posts and got chance to talk to you once as well.

Can you please share what makes you motivated to keep testing/practicing
and how you do manage your professional/personal life along with writing such a good articles.

Obviously, all these things really required time/testing.


jz said...

drop materialized view mv1 preserve table will convert MV to regular table and also preserve all indexes and triggers.


Unknown said...

True. The indexes, triggers, constraints - all continue to exist happily even after the MV is dropped. Yet another reason for using the PREBUILT clause while creatng MVs. said...

Excellent article and great hint. I can add that also privileges seems to be bound with table segment and they are preserved when dropping mview.

yds said...

I am curious about prebuilt option for a while. Your post and some comments enlightened me. Recently, We have a task to build databases which will contain subset data of exsiting databases. I am thinking if using MV approach is feasible. I can set up MV and refresh them from time to time. At cutover, I can convert MV to normal tables. Does it sound feasible?

Unknown said...

Denis - yes, that is exactly the approach Oracle uses for the dbms_redefinition package. So, yours is not only feasible, it's sort of tested as well.

yds said...



I got another question. I wonder do you have any tip about offline instantiation of a large prebuilt-table based MV. That means the master table should be always available for user ( user may do DMLs during exp/imp for example) and it is so big that online complete refresh is not an option.

The Oracle doc is not clear to me at all.

- Denis

Unknown said...

I also noticed that when you do complete refresh, I see Oracle is issuing a delete and then an insert. This is not helping us as delete preserves the high water mark causing it to slowdown over time. Besides it is also generating huge redo. Is there a way to make it issue a truncate ?

John said...

Sorry, I think this tip is not valid. Regardless of whether or not an MV is built on a prebuilt table, you can always alter the underlying table.

I proved it by:
1. create source table xxx, add index, primary key and MV log
2. create MV yyy refresh fast on demand with primary key as select * from xxx
3. insert into xxx
4. dbms_snapshot.refresh(yyy,'FAST')
5. alter table xxx modify a column
6. alter table yyy modify same column
7. insert into xxx
8. dbms_snapshot.refresh(yyy,'FAST')

Works perfectly

Unknown said...

Hi Arup,

I am facing a challenge while creating a Materialized view from a table, which is of close 60 GB. The source table has a CLOB column. The source table belongs to a diffrent application, I got only read only access to the same. The source table has a primary key. If follow the usual approach of createing mv ( i.e. CREATE MATERIALIZED VIEW ABC .... WITH PRIMARY KEY ...AS SELECT * FROM ABC@DBLINK ) . It never completes even after running for 25-30days. It is erroring out saying "Snashot too OLD" . Any suggestion how to proceed here. Thanks in advance . Pratap

Unknown said...

along with other ways to keep your data safe, you may also try the sql server 2000 recovery program, the service of data recovery prevents the corruption of specified files

Unknown said...

@John: the issue is very much valid. The example you gave is the simplest and rudimentary. Note your example:

create MV yyy refresh fast on demand with primary key as select * from xxx

The defining query is "select * from base table", which allows this change. In all cases, the MV is not as simple as "select *", because it is not useful in anyway. MVs are usually defined with a grouping function, or joins with many tables. In that case, you can't alter the column. Here is an example:

SQL> create table src1 (col1 number, col2 number);

Table created.

SQL> alter table src1 add constraint pk_src1 primary key (col1);

Table altered.

SQL> create materialized view mv1
2 refresh complete on demand
3 as
4 select col1, count(1) cnt from src1 group by col1;

Materialized view created.

SQL> alter table mv1 modify (col1 number(2));
alter table mv1 modify (col1 number(2))
ERROR at line 1:
ORA-30556: functional index is defined on the column to be modified

The error is a little strange; but point is that it can't be.

Hope this helps.

Discover your world said...

It's Ok. I am looking how to refresh the newly added column in MV.
I have MV which is created with refresh FAST. MV log is created in master DB. This MV is altered and added a new column and MV is refreshed manually, But it is not populating the new added column. It is refresing the all the rows but not populating the newly added column. Is there any way to do this?

Unknown said...

I think that MV is a bit confusing, Same happens to me when I try to understand what is used viagra online for. I think I have a headache again.

Hichem said...

Hi, We would like to add a LAST_UPDATE_DATE field to the MView because our base table does not have a valid CDC column. We use the PREBUILT approach to add this column as follows: ALTER TABLE table_MV

This works for newly inserted records, which are stamped with the record insert date, but for existing records which are updated, the LAST_UPDATE_DT is not modified. Do you know how to get it updated.

Thanks a lot for your help.

srikanth-rajavaram said...

Hi Arup,

good post. Have a question though. After adding a column, how does the the data for newly added column refresh in the mv?

Will it not require a Full refresh? That is an outage for the application and database. If so, how can we minimize the downtime without doing a complete refresh.


Viagra said...

Great informative article.

Buy Generic Viagra said...

Hello .. I have never seen a smart articles that you created. It really helped me to get back my ideas for writing. I will save this post, for I learn more.

Anonymous said...


It doesnt work.

Could you provide full test-script?

Elliott Broidy said...

Great information. Lots of things I did not know before!