Posted by Whilst the ever increasing speed of storage and servers, and features likes In-memory are decreasing the need for large numbers of materialized views in databases, there are still plenty of use cases where they can be a useful performance or integrity tool.
But what if that materialized view takes minutes or hours to build? Then even if it is fast refresh enabled, than fast refresh is no of use if we have to rebuild the entire materialized view from scratch due to an structural change in the underlying table.
For example, let’s say I have a table and a simple fast refresh materialized view on that table as below:
SQL> create table t(x number(8) primary key);
Table created.
SQL> insert into t values(55);
1 row created.
SQL> create materialized view log on t with primary key, rowid;
Materialized view log created.
SQL> create materialized view mv
2 build immediate
3 refresh fast on demand as
4 select * from t;
Materialized view created.
SQL>
SQL> select * from t;
X
----------
55
1 row selected.
SQL> select * from mv;
X
----------
55
1 row selected.
SQL> insert into t values (10);
1 row created.
SQL> insert into t values (20);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
X
----------
10
20
55
3 rows selected.
SQL> select * from mv;
X
----------
55
1 row selected.
SQL>
SQL> exec dbms_mview.refresh('mv');
PL/SQL procedure successfully completed.
SQL> select * from mv;
X
----------
10
20
55
3 rows selected.
For the sake of this discussion, let’s assume the build of materialized takes hours. Naturally we want to avoid having to a rebuild (or do a complete refresh) of that materialized view. But then…the inevitable happens. We need to change the table T. In this case, the values for column X now exceed the limits of the definition.
SQL> insert into t values (123123123);
insert into t values (123123123)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
Obviously, this is a trivial fix for the table. We simply alter the column to make it larger (which does not require any downtime or reorganization of the data).
SQL> alter table t modify x number(10);
But all is not well…Having a materialized view means that either the materialized view log, or the materialized view itself may have replicas of that column, so they remain “broken”
SQL> insert into t values (123123123);
insert into t values (123123123)
*
ERROR at line 1:
ORA-12096: error in materialized view log on "MCDONAC"."T"
ORA-01438: value larger than specified precision allowed for this column
Notice the subtle difference in the error message. It is not an error on table T, but an error on the materialized view log. You might have the urge to simply jump and run the alter commands on the materialized view log and the materialized view. And you might even find that this approach works. But please note – this approach is not supported, and thus we can’t guarantee that it will (a) work, or (b) not create problems later down the track when you attempt to refresh the view or perform other operations.
But if direct alteration is not support, how do we solve the problem without having to rebuild the entire materialized view from scratch?
The solution here is the option to preserve the materialized view as a standalone table. We can drop the definition of the materialized view but hold on to the table that supports it. Now that it is a standalone table, we can alter the column definition so that it matches our source table T. Notice that before I drop the definition, I perform a final refresh to make sure the materialized is totally up to date – so there is a little bit of coordination required here to make sure that you do not lose any changes that occur to table T during the process.
SQL> exec dbms_mview.refresh('mv');
PL/SQL procedure successfully completed.
SQL> drop materialized view mv preserve table;
Materialized view dropped.
SQL> drop materialized view log on t ;
Materialized view log dropped.
SQL>
SQL> alter table t modify x number(10);
Table altered.
SQL> alter table mv modify x number(10);
Table altered.
We still don’t have our materialized view back though. But we can recreate it without needing a full build cycle, using the PREBUILT table clause.
SQL> create materialized view log on t with PRIMARY KEY, rowid;
Materialized view log created.
SQL>
SQL> create materialized view mv
2 on prebuilt table
3 refresh fast on demand as
4 select * from t;
Materialized view created.
And we are done! An easy and supported means of altering the materialized view structure without a full rebuild of the data.
Learning is not a spectator sport 
Nice one Connor. That’s a useful article.
Thanks! All feedback (good and bad) always appreciated.
On which all DB versions it is applicable
Hello Ashish,
The CREATE MATERIALIZED VIEW … ON PREBUILT TABLE was already available in Oracle 8i.
The DROP MATERIALIZED VIEW .. has the PRESERVE TABLE option only since Oracle9i.
In Oracle8i, as far as I can remember, if the materialized view was created with ON PREBUILT TABLE,
then the table was automatically preserved after DROP MATERIALIZED VIEW, else it was not preserved.
In Oracle9i and higher, by using the PRESERVE TABLE option, you have the possibility to preserve
the underlying table of an mv even if it was NOT created using the ON PREBUILT TABLE
( as in fact is shown in Connor’s example ).
Best Regards,
Iudith
Nice blog improve my knowledge.
I noticed a minute error in one of the sample SQLs. Second insert is into MV not T right?
SQL> insert into t values (123123123);
insert into t values (123123123)
*
ERROR at line 1:
ORA-12096: error in materialized view log on “MCDONAC”.”T”
ORA-01438: value larger than specified precision allowed for this column
No, we’re inserting into the table, but the error is due to the subsequent insert failing on the materialized view log.
Hi Connor,
Thanks for your very informative article. I have a small doubt.
You have mentioned that “We can drop the definition of the materialized view but hold on to the table that supports it. ”
1. Definition of the MVW is the SQL that you have written.
create materialized view mv
2 build immediate
3 refresh fast on demand as
4 select * from t;
2. What is the name of the “table supporting it” — same as the Materialized view which is mv?
Please let me know if my understanding is correct.
materialized views and tables are in different namespaces, which put simply, means they can share a name. So you can a materialized called JOE and a table called JOE that supports the materialized view. So when you drop the materialised view, the table with the same name can remain.
Hi, Connor Nice Blog.
I’ve a question. I’m in the need of create MVs on Big Tables on another database using a Prebuilt Option, my question is Before Exporting and Transfering .dmp from Source to Target database. Do I need first create the MVLOGS on base tables in order to fast refreshing later?