[Oracle]Master表字段扩张时的对应方法

Master表字段扩张时的对应方法

如果Master表的数据量很大，Master表中的列，宽度扩张了，MVIEW表如何对应处理？

此时，重建MVIEW可能会耗费很长的时间。

可以采用 alter materialized view modify 的方式来进行。不过，没有官方文档支持。

create table TEST(id integer, TEST_NUMBER NUMBER(18,6));

insert into TEST
 select i+j, i+j
   from  (
          with DATA2(j) as (
                            select 0 j from DUAL
                              union all
                            select j+1000 from DATA2 where j < 999000
                           )
          select j from DATA2
         ),
         (
          with DATA1(i) as (
                            select 1 i from DUAL
                              union all
                            select i+1 from DATA1 where i < 1000
                           )
          select i from DATA1
         );


ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY(ID);
CREATE MATERIALIZED VIEW LOG ON TEST;
CREATE MATERIALIZED VIEW MV_TEST REFRESH FAST AS SELECT * FROM TEST;

set timing on

如果Master表的字段扩张了 ，可以在MVIEW对应的字段也扩张：

SQL> ALTER MATERIALIZED VIEW MV_TEST MODIFY (TEST_NUMBER NUMBER(22,6));
time elapsed: 00:00:00.08

扩张后确认:
SQL> exec dbms_mview.refresh('MV_TEST');
SQL> exec dbms_mview.refresh('MV_TEST','C');