Yaping's Weblog

September 19, 2008

MATERIALIZED VIEW

Filed under: Oracle — Yaping @ 6:50 am
Tags:

Materialized view basic information

test@TEST>create table test.t1(id number,a char(1),b varchar2(1));
Table created.

test@TEST>create MATERIALIZED VIEW log on test.t1;
create MATERIALIZED VIEW log on test.t1
*
ERROR at line 1:
ORA-12014: table ‘T1′ does not contain a primary key constraint

test@TEST>alter table test.t1 add constraint t1_pk primary key(id);
Table altered.

test@TEST>create MATERIALIZED VIEW log on test.t1;
Materialized view log created.

test@TEST>desc MLOG$_T1
 Name                                                  Null?    Type
 —————————————————– ——– ————————————
 ID                                                             NUMBER
 SNAPTIME$$                                                     DATE
 DMLTYPE$$                                                      VARCHAR2(1)
 OLD_NEW$$                                                      VARCHAR2(1)
 CHANGE_VECTOR$$                                                RAW(255)

test@TEST>desc RUPD$_T1
 Name                                                  Null?    Type
 —————————————————– ——– ————————————
 ID                                                             NUMBER
 DMLTYPE$$                                                      VARCHAR2(1)
 SNAPID                                                         NUMBER(38)
 CHANGE_VECTOR$$                                                RAW(255)

test@TEST>insert into test.t1 values(1,’A’,’B’);
1 row created.

test@TEST>delete from test.t1;
1 row deleted.

test@TEST>select ID,DMLTYPE$$,OLD_NEW$$ from MLOG$_T1;
        ID D O
———- – –
         1 I N
         1 D O

test@TEST>select * from RUPD$_T1;
no rows selected

 
The default clause on create materialized view log is with primary key, so if no clause be specified, primary key on the base table must exist.
The definition of materialized view log (MLOG$_) depends on the specified clause.
Table RUPD$_ only exists when materialized view log uses primary key clause, it’s a special log for updatable snapshots, which would make sense in the context of 2 way replication.

 
test@TEST>drop  MATERIALIZED VIEW log on test.t1;
Materialized view log dropped.

test@TEST>create MATERIALIZED VIEW log on test.t1 with rowid, SEQUENCE;
Materialized view log created.

test@TEST>desc MLOG$_T1
 Name                                                  Null?    Type
 —————————————————– ——– ————————————
 M_ROW$$                                                        VARCHAR2(255)
 SEQUENCE$$                                                     NUMBER
 SNAPTIME$$                                                     DATE
 DMLTYPE$$                                                      VARCHAR2(1)
 OLD_NEW$$                                                      VARCHAR2(1)
 CHANGE_VECTOR$$                                                RAW(255)

test@TEST>desc RUPD$_T1
ERROR:
ORA-04043: object RUPD$_T1 does not exist

test@TEST>create table test.t2(id number,a char(1), b varchar2(1));
Table created.

test@TEST>create MATERIALIZED VIEW log on test.t2 with rowid, SEQUENCE;
Materialized view log created.

test@TEST>insert into test.t1 values(2,’A’,’B’);
1 row created.

test@TEST>insert into test.t2 values(1,’A’,’B’);
1 row created.

test@TEST>update test.t1 set a=’C’ where id=2;
1 row updated.

test@TEST>select M_ROW$$,SEQUENCE$$,DMLTYPE$$,OLD_NEW$$ from MLOG$_T1;
M_ROW$$              SEQUENCE$$ D O
——————– ———- – –
AAAOCJAAFAAAAAyAAB           23 I N
AAAOCJAAFAAAAAyAAB           25 U U
test@TEST>select M_ROW$$,SEQUENCE$$,DMLTYPE$$,OLD_NEW$$ from MLOG$_T2;
M_ROW$$              SEQUENCE$$ D O
——————– ———- – –
AAAOCOAAFAAAAAiAAA           24 I N
The column SEQUENCE$$ on MLOG$_ records the base table’s changing sequence, all materialized view logs use the same sequence to generate the value. It help Oracle apply updates to materialized view logs in the correct order when a mix of DML commands, e.g. insert, update and delete, are performed on multiple base tables in a single transaction.

test@TEST>drop  MATERIALIZED VIEW log on test.t1;
Materialized view log dropped.

test@TEST>create MATERIALIZED VIEW log on test.t1 with PRIMARY KEY, rowid, SEQUENCE(a,b) including new values;
Materialized view log created.

test@TEST>desc MLOG$_T1
 Name                                                  Null?    Type
 —————————————————– ——– ————————————
 ID                                                             NUMBER
 A                                                              CHAR(1)
 B                                                              VARCHAR2(1)
 M_ROW$$                                                        VARCHAR2(255)
 SEQUENCE$$                                                     NUMBER
 SNAPTIME$$                                                     DATE
 DMLTYPE$$                                                      VARCHAR2(1)
 OLD_NEW$$                                                      VARCHAR2(1)
 CHANGE_VECTOR$$                                                RAW(255)

test@TEST>desc RUPD$_T1
 Name                                                  Null?    Type
 —————————————————– ——– ————————————
 ID                                                             NUMBER
 DMLTYPE$$                                                      VARCHAR2(1)
 SNAPID                                                         NUMBER(38)
 CHANGE_VECTOR$$                                                RAW(255)

test@TEST>CREATE MATERIALIZED VIEW test.mv_t1 build immediate refresh force on demand
  2  as select * from test.t1;
Materialized view created.

test@TEST>select object_id,object_name,object_type from user_objects where OBJECT_TYPE not in (’PROCEDURE’,’FUNCTION’,’DATABASE LINK’);
 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
———- —————————— ——————-
     57482 T1_PK                          INDEX
     57505 RUPD$_T1                       TABLE
     57506 MV_T1                          TABLE
     57507 T1_PK1                         INDEX
     57508 MV_T1                          MATERIALIZED VIEW
     57504 MLOG$_T1                       TABLE
     57481 T1                             TABLE
7 rows selected.
After create materialized view, one materialized view, one table with the same name as materialized view, one index on the table are created automatically.
Materialized view refresh

test@TEST>insert into test.t1 values(1,’A’,’B’);
1 row created.

test@TEST>update test.t1 set a=’a’,b=’b’ where id=2;
1 row updated.

test@TEST>commit;
Commit complete.

test@TEST>insert into test.t1 values(3,’A’,’B’);
1 row created.

test@TEST>select ID,A,B,M_ROW$$,SEQUENCE$$,DMLTYPE$$,OLD_NEW$$ from MLOG$_T1;
        ID A B M_ROW$$              SEQUENCE$$ D O
———- – – ——————– ———- – –
         1 A B AAAOCJAAFAAAAAyAAC           26 I N
         2 C B AAAOCJAAFAAAAAyAAB           27 U O
         2 a b AAAOCJAAFAAAAAyAAB           28 U N
         3 A B AAAOCJAAFAAAAAyAAA           29 I N

test@TEST>alter session set events ‘10046 trace name context forever,level 12′;
Session altered.

test@TEST>exec dbms_mview.refresh(’MV_T1′,’F’);
PL/SQL procedure successfully completed.

test@TEST>select * from mv_t1;
        ID A B
———- – –
         2 a b
         1 A B
         3 A B

sys@TEST>select * from test.t1;
        ID A B
———- – –
         3 A B
         2 a b
         1 A B

PARSING IN CURSOR #5 len=45 dep=0 uid=66 oct=47 lid=66 tim=1177042417020396 hv=3981352777 ad=’288fbcc8′
BEGIN dbms_mview.refresh(’MV_T1′,’F’); END;
END OF STMT
PARSE #5:c=999,e=1439,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1177042417020386
BINDS #5:
=====================
PARSING IN CURSOR #10 len=11 dep=1 uid=0 oct=44 lid=0 tim=1177042417021593 hv=1180858989 ad=’0′
COMMIT WORK
END OF STMT
PARSE #10:c=0,e=126,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=1177042417021584
XCTEND rlbk=0, rd_only=0
EXEC #10:c=3000,e=3190,p=0,cr=0,cu=1,mis=0,r=0,dep=1,og=0,tim=1177042417024940

PARSING IN CURSOR #23 len=241 dep=1 uid=66 oct=3 lid=66 tim=1177042417243005 hv=1545147654 ad=’289d935c’
SELECT DISTINCT LOG$.”ID” FROM (SELECT MLOG$.”ID” FROM “TEST”.”MLOG$_T1″ MLOG$ WHERE “SNAPTIME$$” > :1 AND (”DMLTYPE$$” != ‘I’)) LOG$ WHERE (LOG$.”ID”) NOT IN (SELECT MAS_TAB$.”ID” FROM “TEST”.”T1″ “MAS_TAB$” WHERE LOG$.”ID” = MAS_TAB$.”ID”)
END OF STMT
PARSE #23:c=1000,e=649,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1177042417243001

PARSING IN CURSOR #23 len=277 dep=1 uid=66 oct=3 lid=66 tim=1177042417256460 hv=354087628 ad=’27e4eba0′
SELECT CURRENT$.”ID”,CURRENT$.”A”,CURRENT$.”B” FROM (SELECT “T1″.”ID” “ID”,”T1″.”A” “A”,”T1″.”B” “B” FROM “TEST”.”T1″ “T1″) CURRENT$, (SELECT DISTINCT MLOG$.”ID” FROM “TEST”.”MLOG$_T1″ MLOG$ WHERE “SNAPTIME$$” > :1 AND (”DMLTYPE$$” != ‘D’)) LOG$ WHERE CURRENT$.”ID” = LOG$.”ID”
END OF STMT
PARSE #23:c=999,e=554,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1177042417256456

PARSING IN CURSOR #14 len=69 dep=2 uid=66 oct=6 lid=66 tim=1177042417280452 hv=1853616947 ad=’27d0c5f0′
UPDATE “TEST”.”MV_T1″ SET “ID” = :1,”A” = :2,”B” = :3 WHERE “ID” = :1
END OF STMT
PARSE #14:c=0,e=226,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,tim=1177042417280445
BINDS #14:
kkscoacd
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b70f5484  bln=22  avl=02  flg=09
  value=1
 Bind#1
  oacdty=96 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=b70f549c  bln=32  avl=01  flg=09
  value=”A”
 Bind#2
  oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=b70f54a0  bln=32  avl=01  flg=09
  value=”B”
 Bind#3
  No oacdef for this bind.
EXEC #14:c=3999,e=5391,p=0,cr=2,cu=0,mis=1,r=0,dep=2,og=1,tim=1177042417285916

PARSING IN CURSOR #22 len=60 dep=2 uid=66 oct=2 lid=66 tim=1177042417286092 hv=1178564284 ad=’27e1d10c’
INSERT INTO “TEST”.”MV_T1″  (”ID”,”A”,”B”) VALUES (:1,:2,:3)
END OF STMT
PARSE #22:c=0,e=130,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,tim=1177042417286087
BINDS #22:
kkscoacd
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b70f5484  bln=22  avl=02  flg=09
  value=1
 Bind#1
  oacdty=96 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=b70f549c  bln=32  avl=01  flg=09
  value=”A”
 Bind#2
  oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=b70f54a0  bln=32  avl=01  flg=09
  value=”B”
EXEC #22:c=1000,e=678,p=0,cr=1,cu=8,mis=1,r=1,dep=2,og=1,tim=1177042417286879
EXEC #22:c=1000,e=678,p=0,cr=1,cu=8,mis=1,r=1,dep=2,og=1,tim=1177042417286879
BINDS #14:
kkscoacd
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b70f5484  bln=22  avl=02  flg=09
  value=2
 Bind#1
  oacdty=96 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=b70f549c  bln=32  avl=01  flg=09
  value=”a”
 Bind#2
  oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=b70f54a0  bln=32  avl=01  flg=09
  value=”b”
 Bind#3
  No oacdef for this bind.
EXEC #14:c=0,e=313,p=0,cr=1,cu=3,mis=0,r=1,dep=2,og=1,tim=1177042417287291
BINDS #14:
kkscoacd
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b70f5484  bln=22  avl=02  flg=09
  value=3
 Bind#1
  oacdty=96 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=b70f549c  bln=32  avl=01  flg=09
  value=”A”
 Bind#2
  oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=b70f54a0  bln=32  avl=01  flg=09
  value=”B”
 Bind#3
  No oacdef for this bind.
EXEC #14:c=1000,e=1185,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=1,tim=1177042417288518
BINDS #22:
kkscoacd
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=b70f5484  bln=22  avl=02  flg=09
  value=3
 Bind#1
  oacdty=96 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=b70f549c  bln=32  avl=01  flg=09
  value=”A”
 Bind#2
  oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=b70f54a0  bln=32  avl=01  flg=09
  value=”B”
EXEC #22:c=0,e=180,p=0,cr=0,cu=3,mis=0,r=1,dep=2,og=1,tim=1177042417288728

PARSING IN CURSOR #14 len=52 dep=1 uid=0 oct=7 lid=0 tim=1177042417319986 hv=271018863 ad=’28993e00′
delete from “TEST”.”MLOG$_T1″ where snaptime$$ <= :1
END OF STMT
PARSE #14:c=999,e=1093,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1177042417319982
BINDS #14:
kkscoacd
 Bind#0
  oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=00 csi=00 siz=8 off=0
  kxsbbbfp=bfff9cf0  bln=07  avl=07  flg=09
  value=”3/12/2008 11:10:35″
EXEC #14:c=1000,e=1636,p=0,cr=3,cu=4,mis=1,r=4,dep=1,og=4,tim=1177042417321698

 
Even though the MLOG$_ table contains the old and new values, the materialized view obtains the current value through access the base table.

 

Change base table’s definition

sys@TEST>exec DBMS_REDEFINITION.CAN_REDEF_TABLE(’TEST’,’T1′);
BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE(’TEST’,’T1′); END;
*
ERROR at line 1:
ORA-12091: cannot online redefine table “TEST”.”T1″ with materialized views
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 137
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 1478
ORA-06512: at line 1

test@TEST>insert into test.t1 values(4,’A’,’B’);
1 row created.

test@TEST>alter table test.t1 modify b varchar2(2);
Table altered.

test@TEST>insert into test.t1 values(5,’A’,’BB’);
insert into test.t1 values(5,’A’,’BB’)
                 *
ERROR at line 1:
ORA-12096: error in materialized view log on “TEST”.”T1″
ORA-12899: value too large for column “TEST”.”MLOG$_T1″.”B” (actual: 2, maximum: 1)

test@TEST>insert into test.t1 values(5,’A’,’B’);
1 row created.

test@TEST>exec dbms_mview.refresh(’MV_T1′,’F’);
PL/SQL procedure successfully completed.

test@TEST>alter  MATERIALIZED VIEW log on test.t1 modify b varchar2(2);
Materialized view log altered.

test@TEST>insert into test.t1 values(6,’A’,’BB’);
1 row created.

test@TEST>exec dbms_mview.refresh(’MV_T1′,’F’);
BEGIN dbms_mview.refresh(’MV_T1′,’F’); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-12899: value too large for column “TEST”.”MV_T1″.”B” (actual: 2, maximum: 1)
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2254
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2460
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2429
ORA-06512: at line 1

test@TEST>desc test.MV_T1
 Name                                                  Null?    Type
 —————————————————– ——– ————————————
 ID                                                    NOT NULL NUMBER
 A                                                              CHAR(1)
 B                                                              VARCHAR2(1)
 
test@TEST>alter table test.mv_t1 modify b varchar2(2);
Table altered.

test@TEST>exec dbms_mview.refresh(’MV_T1′,’F’);
PL/SQL procedure successfully completed.

test@TEST>alter table test.t1 modify a char(2);
alter table test.t1 modify a char(2)
*
ERROR at line 1:
ORA-12096: error in materialized view log on “TEST”.”T1″
ORA-12899: value too large for column “TEST”.”MLOG$_T1″.”A” (actual: 2, maximum: 1)

test@TEST>alter  MATERIALIZED VIEW log on test.t1 modify a char(2);
Materialized view log altered.

test@TEST>alter table test.t1 modify a char(2);
Table altered.

test@TEST>alter table test.mv_t1 modify a char(2);
alter table test.mv_t1 modify a char(2)
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

test@TEST>drop MATERIALIZED VIEW mv_t1;
Materialized view dropped.

test@TEST>drop MATERIALIZED VIEW log on test.t1;
Materialized view log dropped.

test@TEST>create MATERIALIZED VIEW log on test.t1 with PRIMARY KEY, rowid, SEQUENCE(a,b) including new values;
Materialized view log created.

test@TEST>CREATE MATERIALIZED VIEW test.mv_t1 build immediate refresh force on demand as select * from test.t1;
Materialized view created.

test@TEST>select * from test.mv_t1;
        ID A  B
———- — –
         3 A  B
         2 a  b
         1 A  B
         4 A  B
         5 A  B
         6 A  BB
6 rows selected.

test@TEST>insert into test.t1(id) values(7);
1 row created.

test@TEST>alter table test.t1 modify b default ‘NA’;
Table altered.
 
test@TEST>exec dbms_mview.refresh(’MV_T1′,’F’);
PL/SQL procedure successfully completed.

test@TEST>insert into test.t1(id) values(8);
1 row created.

test@TEST>alter table test.t1 modify a default ‘NA’;
Table altered.

test@TEST>exec dbms_mview.refresh(’MV_T1′,’F’);
PL/SQL procedure successfully completed.

test@TEST>select * from test.mv_t1;
        ID A  B
———- — –
         3 A  B
         2 a  b
         1 A  B
         4 A  B
         5 A  B
         6 A  BB
         7
         8    NA
8 rows selected.

test@TEST>alter table test.t1 drop column a;
Table altered.

test@TEST>exec dbms_mview.refresh(’MV_T1′,’F’);
PL/SQL procedure successfully completed.

test@TEST>select * from mv_t1;
        ID A  B
———- — –
         3 A  B
         2 a  b
         1 A  B
         4 A  B
         5 A  B
         6 A  BB
         9 NA NA
         7
         8    NA
9 rows selected.

test@TEST>exec dbms_mview.refresh(’MV_T1′,’C’);
BEGIN dbms_mview.refresh(’MV_T1′,’C’); END;
*
ERROR at line 1:
ORA-12018: following error encountered during code generation for “TEST”.”MV_T1″
ORA-00904: “T1″.”A”: invalid identifier
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2254
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2460
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2429
ORA-06512: at line 1

test@TEST>alter table test.MLOG$_T1 drop column a;
Table altered.

test@TEST>alter table test.mv_t1 drop column a;
Table altered.

test@TEST>insert into test.t1(id) values(10);
1 row created.

test@TEST>exec dbms_mview.UNREGISTER_MVIEW(’TEST’,’MV_T1′,’TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM’);
PL/SQL procedure successfully completed.

test@TEST>exec dbms_mview.REGISTER_MVIEW(’TEST’,’MV_T1′,’TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM’,100,dbms_mview.reg_primary_key_mview,’select id,b from test.t1′,dbms_mview.reg_v8_snapshot);
PL/SQL procedure successfully completed.

sys@TEST>update sys.snap$ set QUERY_TXT=’SELECT “T1″.”ID” “ID”,”T1″.”B” “B” FROM “TEST”.”T1″ “T1″‘,QUERY_LEN=56 where VNAME=’MV_T1′;
1 row updated.

sys@TEST>commit;
Commit complete.

sys@TEST>exec dbms_mview.refresh(’TEST.MV_T1′,’F’);
PL/SQL procedure successfully completed.

sys@TEST>select * from test.mv_t1;
        ID B
———- –
         3 B
         2 b
         1 B
         4 B
         5 B
         6 BB
         9 NA
         7
         8 NA
        10 NA
10 rows selected.
From the above result, the base table can change the column’s length on some data type, such as varchar2, but not char. There’s different behavior to specify the default value on varchar2 and char after the columns have exist. Drop base table’s columns which be referenced by materialized view maybe be possible (Don’t do that on production). Add columns to base table which materialized view does not contains them also be possible. If multi-tables join or contain aggregation function, there’s more restriction to change base table’s definition.

 
Move materialized view to different tablespace.

test@TEST>alter table test.mv_t1 move tablespace users;
Table altered.

test@TEST>alter index t1_pk1 rebuild;
Index altered.

 
Complete refresh changed from 9i to 10g
Materialized view complete refresh has changed from Oracle 9i to 10g a little. Also atomic parameter has the same default value, but the behavior is different when complete refresh one materialized view. After upgrade from Oracle 9i to 10g, complete refresh maybe take a long time and more redo size.

SQL> select * from v$version;
BANNER
—————————————————————-
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
PL/SQL Release 9.2.0.4.0 – Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 – Production
NLSRTL Version 9.2.0.4.0 – Production
 
SQL> create table t1 as select rownum id from all_objects where rownum<6;
Table created.
 
SQL> alter table t1 add constraint t1_pk primary key (id);
Table altered.

SQL> create materialized view mv_t1 as select * from t1;
Materialized view created.

SQL> select * from mv_t1;
ID
———-
1
2
3
4
5

SQL> alter session set events ‘10046 trace name context forever,level 12′;
Session altered.

SQL> exec dbms_mview.refresh(’MV_T1′,method=>’c’);
PL/SQL procedure successfully completed.

SQL> alter session set events ‘10046 trace name context off’;
Session altered.
Extract data from trace file.

PARSING IN CURSOR #31 len=48 dep=1 uid=22 oct=85 lid=22 tim=1173899130266695 hv=1203315168 ad=’5939c468′
truncate table “TEST”.”MV_T1″ purge snapshot log
END OF STMT
PARSE #31:c=0,e=11549,p=0,cr=0,cu=1,mis=1,r=0,dep=1,og=4,tim=1173899130266689

PARSING IN CURSOR #31 len=78 dep=1 uid=22 oct=2 lid=22 tim=1173899130361469 hv=2194498332 ad=’59373ac4′
INSERT /*+ APPEND */ INTO “TEST”.”MV_T1″(”ID”) SELECT “T1″.”ID” FROM “T1″ “T1″
END OF STMT

From the above result, the materialized view be truncated firstly, then insert append data, if this materialized view is nologging, it maybe cause recover issue after that time.

SQL> select NAME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME from v$datafile;
NAME UNRECOVERABLE_CHANGE# UNRECOVER
————————————————– ——————— ———
/ddms01/dms/o02dms0suprt1/system_1.O02DMS0 0
/ddms01/dms/o02dms0data1/undo_tbs_01.O02DMS0 0
/ddms01/dms/o02dms0data1/users_1.O02DMS0 0
/ddms01/dms/o02dms0data1/cqowner_ts_01.O02DMS0 0
/ddms01/dms/o02dms0data1/tools01.dbf 0
/ddms01/dms/o02dms0data1/test_2k_01.dbf 0
/ddms01/dms/o02dms0data1/test01.dbf 0
/ddms01/dms/oracle/9.2.0/dbs/MISSING00008 0
8 rows selected.

SQL> drop materialized view mv_t1;
Materialized view dropped.

SQL> create materialized view mv_t1 nologging tablespace users as select * from t1;
Materialized view created.

SQL> select NAME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME from v$datafile;
NAME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME
————————————————– ——————— ——————-

/ddms01/dms/o02dms0data1/users_1.O02DMS0 1.1329E+10 2008-02-03 13:16:57

SQL> exec dbms_mview.refresh(’MV_T1′,method=>’c’);
PL/SQL procedure successfully completed.

SQL> select NAME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME from v$datafile;
NAME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME
————————————————– ——————— ——————-

/ddms01/dms/o02dms0data1/users_1.O02DMS0 1.1329E+10 2008-02-03 13:19:09

SQL> create materialized view mv2_t1 as select * from t1;
Materialized view created.

SQL> alter session set events ‘10046 trace name context forever,level 12′;
Session altered.

SQL> exec dbms_mview.refresh(’MV_T1,MV2_t1′,method=>’c’);
PL/SQL procedure successfully completed.


PARSING IN CURSOR #18 len=26 dep=1 uid=22 oct=7 lid=22 tim=1173901762258356 hv=2659115118 ad=’5af5f0e0′
delete from “TEST”.”MV_T1″
END OF STMT

PARSING IN CURSOR #18 len=64 dep=1 uid=22 oct=2 lid=22 tim=1173901762261353 hv=3235319373 ad=’593e7a60′
INSERT INTO “TEST”.”MV_T1″(”ID”) SELECT “T1″.”ID” FROM “T1″ “T1″
END OF STMT

PARSING IN CURSOR #18 len=27 dep=1 uid=22 oct=7 lid=22 tim=1173901762287626 hv=2749552578 ad=’5aee50f0′
delete from “TEST”.”MV2_T1″
END OF STMT

PARSING IN CURSOR #20 len=65 dep=1 uid=22 oct=2 lid=22 tim=1173901762283788 hv=1756284514 ad=’5aee9ec4′
INSERT INTO “TEST”.”MV2_T1″(”ID”) SELECT “T1″.”ID” FROM “T1″ “T1″
END OF STMT
 

If complete refresh a group of materialized view, deleting instead of truncate data so that all actions are in one transaction.

SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

SQL> create table t1 as select rownum id from all_objects where rownum<6;
Table created.

SQL> alter table t1 add constraint t1_pk primary key (id);
Table altered.
 
SQL> create materialized view mv_t1 as select * from t1;
Materialized view created.

SQL> alter session set events ‘10046 trace name context forever,level 12′;
Session altered.

SQL> exec dbms_mview.refresh(’MV_T1′,method=>’c’);
PL/SQL procedure successfully completed.

SQL> alter session set events ‘10046 trace name context off’;
Session altered.


PARSING IN CURSOR #21 len=27 dep=1 uid=32 oct=7 lid=32 tim=1173900388085768 hv=2156255936 ad=’2f8da41c’
delete from “TEST”.”MV_T1″
END OF STMT

PARSING IN CURSOR #19 len=64 dep=1 uid=32 oct=2 lid=32 tim=1173900387983740 hv=1185993576 ad=’2f960aac’
INSERT INTO “TEST”.”MV_T1″(”ID”) SELECT “T1″.”ID” FROM “T1″ “T1″
END OF STMT

From the above result, we can see that the default behavior is different between 9i and 10g when complete refersh one materialized view.

SQL> alter session set events ‘10046 trace name context forever,level 12′;
Session altered.

SQL> exec dbms_mview.refresh(’MV_T1′,method=>’c’,atomic_refresh=>false);
PL/SQL procedure successfully completed.

PARSING IN CURSOR #21 len=49 dep=1 uid=32 oct=85 lid=32 tim=1173903443911624 hv=1821482286 ad=’2c538e0c’
truncate table “TEST”.”MV_T1″ purge snapshot log
END OF STMT

PARSING IN CURSOR #21 len=123 dep=1 uid=32 oct=2 lid=32 tim=1173903444814792 hv=2267269472 ad=’2f87a8cc’
INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO “TEST”.”MV_T1″(”ID”) SELECT “T1″.”ID” FROM “T1″ “T1″
END OF STMT

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: