Thanks for the question, Ashish.

Asked: January 27, 2016 - 9:25 am UTC

Last updated: August 20, 2018 - 4:29 am UTC

Version: 11

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I have added a new column in my master table and i want this column to be reflected in my Materialized view is this possible without Re creating it
i have to fetch all columns from my master table in my view.please suggest.

and Chris said...

You can't add columns to an MV. You need to drop and recreate it. Or create a new one alongside and switch to that.

To help reduce the time it takes to rebuild the MV, you can drop it, preserving the table. Then add the column(s) to the base table and the preserved MV table. Then re-create the MV from the table:

create table t (x integer primary key);
create materialized view log on t;
insert into t values (1);

commit;

create materialized view mv as 
  select * from t;
  
select * from mv;

         X
----------
         1

drop materialized view mv preserve table;

select * from mv;

         X
----------
         1

alter table t add (y integer);
alter table mv add (y integer);

create materialized view mv on prebuilt table 
  refresh fast on demand
as 
  select * from t;

insert into t values (2, 2);
commit;

select * from t;  

         X          Y
---------- ----------
         1           
         2          2

select * from mv;

         X          Y
---------- ----------
         1 

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

select * from mv;

         X          Y
---------- ----------
         1           
         2          2

You will lose any changes to T between you "dropping" the MV and re-creating it. If this will be an issue for you, just do a complete refresh of the MV at the end.

Rating

(6 ratings)

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

Comments

Materialized view

Ashish Dabral, January 27, 2016 - 10:43 am UTC

thanks Chris,

my master table has around billions of records .it took long time to do complete refresh.

January 27, 2016 - 5:05 pm UTC

Do you need to do a complete refresh or can you do a fast refresh?

parallelism

Rajeshwaran, Jeyabal, January 27, 2016 - 1:54 pm UTC

my master table has around billions of records .it took long time to do complete refresh.

exec dbms_mview.refresh('MV','F',parallelism=>4);

January 27, 2016 - 5:05 pm UTC

Yep, parallelism could help

Adding columns to MViews

Rajeshwaran, Jeyabal, January 27, 2016 - 2:37 pm UTC

You can't add columns to an MV. You need to drop and recreate it.

How about adding columns to "tables" that support mviews. don't that work here ?

rajesh@ORA11G> set feedback off
rajesh@ORA11G> create table t(x int primary key);
rajesh@ORA11G> insert into t values(55);
rajesh@ORA11G> create materialized view log on t ;
rajesh@ORA11G> create materialized view mv
  2  build immediate
  3  refresh on demand
  4  enable query rewrite as
  5  select * from t;
rajesh@ORA11G> set feedback on
rajesh@ORA11G> select * from t;

         X
----------
        55

1 row selected.

rajesh@ORA11G> select * from mv;

         X
----------
        55

1 row selected.

rajesh@ORA11G> alter table t add x2 int;

Table altered.

rajesh@ORA11G> alter table mv add x2 int;

Table altered.

rajesh@ORA11G> select * from t;

         X         X2
---------- ----------
        55

1 row selected.

rajesh@ORA11G> select * from mv;

         X         X2
---------- ----------
        55

1 row selected.

rajesh@ORA11G> select mview_name,staleness
  2  from user_mviews
  3  where mview_name ='MV' ;

MVIEW_NAME                     STALENESS
------------------------------ -------------------
MV                             FRESH

1 row selected.

rajesh@ORA11G>

January 27, 2016 - 5:08 pm UTC

This may add the column, but the MV doesn't populate it from the table. Continuing your example:

insert into t values (2, 1);
commit;
exec dbms_mview.refresh('MV', 'C');
select * from t;

         X         X2
---------- ----------
         2          1
        55

select * from mv;

         X         X2
---------- ----------
         2           
        55

X2 isn't populated in the MV!

Adding a column that already exists in the base table

David, January 16, 2017 - 4:39 pm UTC

If the base table had an already populated column and that column was added to the mv table and the mv, would the mv update the values for that column in the mv table?

create table t (x integer primary key, y integer);
create materialized view log on t;
insert into t values (1, 10);

commit;

create materialized view mv as 
  select x from t;
  
select * from mv;

         X
----------
         1

drop materialized view mv preserve table;

select * from mv;

         X
----------
         1

create materialized view mv on prebuilt table 
  refresh fast on demand
as 
  select x, y from t;

select * from t;  

         X          Y
---------- ----------
         1         10 

select * from mv;

         X          Y
---------- ----------
         1 

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

would the below be true?
select * from mv;

         X          Y
---------- ----------
         1         10

January 16, 2017 - 5:48 pm UTC

I'm not following your example. You need to add the column to the MV table you preserved:

create table t (x integer primary key, y integer);
create materialized view log on t;
insert into t values (1, 10);

commit;

create materialized view mv as 
  select x from t;
  
select * from mv;

X  
1 

drop materialized view mv preserve table;

select * from mv;

X  
1 

create materialized view mv on prebuilt table 
  refresh fast on demand
as 
  select x, y from t;

SQL Error: ORA-12060: shape of prebuilt table does not match definition query

alter table mv add y int;

create materialized view mv on prebuilt table 
  refresh fast on demand
as 
  select x, y from t;

set null <null>
select * from mv;

X  Y       
1  <null>

Then do a complete refresh of the MV to pick up the new values:

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

select * from mv;

X  Y       
1  <null> 

exec dbms_mview.refresh('MV', 'C');

select * from mv;

X  Y   
1  10

Adding a column that already exists in the base table

David, January 16, 2017 - 5:58 pm UTC

Sorry I forgot the

alter table mv add (y integer);

step.

Your answer about the complete refresh answers my question.

How about MODIFY COLUMN

Edward Hayrabedian, August 16, 2018 - 3:10 pm UTC

Hi team,

A column called "ID" part of the table "T" must be altered from NUMBER(10) to NUMBER(20). Unfortunately, a materialized view "MV_T" is defined on top of the table "T", and to make things worse we have the MV_T materialized view primary key consists of the column ID. and yes, we have a materialized view log which also includes the PK column :)

Without knowing that a modification of a materialized view is so restricted, i was able to user several "ALTER" statements and bring it into live. Then, check the documentation and was surprised that it is not supported at all :( Can you, please, elaborate ? Did I just get lucky being able to do so ?! Below is the detailed test case:

~~~~~~~~~~~~
/*
drop materialized view log on t;
drop materialized view mv_t;
drop table t;
*/

create table t (id number(3));
alter table t add constraint t_pk primary key (id);

insert into t values (333);
insert into t values (4444);
-->> ORA-01438: value larger than specified precision allowed for this column

create materialized view log on t with PRIMARY KEY, rowid;

create materialized view mv_t (id)
refresh fast
with PRIMARY KEY
as select * from t;

-- Now, time for base table's structural change :
alter table t modify id number(4);

insert into t values (4444);
-- ORA-12096: error in materialized view log on "xxx"."T"
-- ORA-01438: value larger than specified precision allowed for this column

select last_refresh_date, compile_state from user_mviews where mview_name = 'MV_T';
-- 16-08-2018 17:31:49 NEEDS_COMPILE

alter materialized view mv_t compile;

select last_refresh_date, compile_state from user_mviews where mview_name = 'MV_T';
-- 16-08-2018 17:31:49 COMPILATION_ERROR

-- so, let's alter the MV structure either:
alter materialized view mv_t modify id number(4);

select last_refresh_date, compile_state from user_mviews where mview_name = 'MV_T';
-- 16-08-2018 17:31:49 COMPILATION_ERROR

alter materialized view mv_t compile;

select last_refresh_date, compile_state from user_mviews where mview_name = 'MV_T';
-- 16-08-2018 17:31:49 VALID

-- try NUMBER(4) insert again:
insert into t values (4444);
-- ORA-12096: error in materialized view log on "xxx"."T"
-- ORA-01438: value larger than specified precision allowed for this column

-- now, the MV log is complaining as I forgot about it :), but let's fix it:
alter materialized view log on t modify id number(4);

insert into t values (4444);
-- 1 row inserted.
commit;

begin
dbms_mview.refresh('MV_T');
end;
/

select * from mv_t;
-- 333
-- 4444

August 20, 2018 - 4:29 am UTC

Yeah that's a tough one to answer.

Your approach seems valid but of course if officially outside the bounds of what we Support, so the way I would tackle it is:

SQL> create table t(x number(2) 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> insert into t values (999);
insert into t values (999)
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL>
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(5);

Table altered.

SQL> alter table mv modify x number(5);

Table altered.

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.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Materialized view

parallelism

Adding columns to MViews

Adding a column that already exists in the base table

Adding a column that already exists in the base table

How about MODIFY COLUMN

More to Explore

DBMS_MVIEW

Materialized View Add Columns

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Materialized view

parallelism

Adding columns to MViews

Adding a column that already exists in the base table

Adding a column that already exists in the base table

How about MODIFY COLUMN

More to Explore

DBMS_MVIEW