* A simple materilaized join view. MJV can be fast refreshed on commit.
The important fact here is that all ORWID from the detail tables must be included
in the select list.
* Note that column c2 is not inculded in the MV LOG, yet changes to this coulmn can be fast refreshed -
in this simple 2-table join. Refer to the "filter columns" page for more details.
* What is ORACLE doing when the join view is fast refreshed?
SQL> select * from t2_a;
C1 C2
-- ----------
a 1
b 2
SQL> select * from t3_a;
C1 C2
-- ----------
b 7
c 8
SQL> alter table t2_a add constraint t2_a_pk primary key (c1);
Table altered.
SQL> alter table t3_a add constraint t3_a_pk primary key (c1);
Table altered.
SQL>
SQL> create materialized view log on t2_a
2 with primary key
3 including new values;
Materialized view log created.
SQL>
SQL> create materialized view log on t3_a
2 with primary key
3 including new values;
Materialized view log created.
SQL>
SQL> -- Must include ROWID
SQL> alter materialized view log on t2_a add rowid;
Materialized view log altered.
SQL> alter materialized view log on t3_a add rowid;
Materialized view log altered.
SQL>
SQL> -- Must include ROWID from both tables
SQL> create materialized view mv_t1_a_j_t2_a
2 refresh fast on commit
3 as select t2_a.c1, t3_a.c2, t2_a.rowid as t2rowid
4 from t2_a, t3_a where t2_a.c1=t3_a.c1;
from t2_a, t3_a where t2_a.c1=t3_a.c1
*
ERROR at line 4:
ORA-12052: cannot fast refresh materialized view JYANG.MV_T1_A_J_T2_A
SQL>
SQL> create materialized view mv_t1_a_j_t2_a
2 refresh fast on commit
3 as select t2_a.c1, t3_a.c2, t2_a.rowid as t2rowid, t3_a.rowid as t3rowid
4 from t2_a, t3_a where t2_a.c1=t3_a.c1;
Materialized view created.
SQL>
SQL> select * from mv_t1_a_j_t2_a;
C1 C2 T2ROWID T3ROWID
-- ---------- ------------------ ------------------
b 7 AAANysAAJAAAARIAAB AAANytAAJAAAAWoAAA
SQL> -- Insert a row that should not affect the MV
SQL> insert into t2_a values('w',9);
1 row created.
SQL> select * from mlog$_t2_a;
C1 SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$
-- -------------------- ---------- ---------- ----------------
M_ROW$$
--------------------------------------------------------------------------------
w 010100 00:00:00 I N FE
AAANysAAJAAAARIAAC
SQL> select * from mlog$_t3_a;
no rows selected
SQL> commit;
Commit complete.
SQL> -- No change in the MV
SQL> select * from mv_t1_a_j_t2_a;
C1 C2 T2ROWID T3ROWID
-- ---------- ------------------ ------------------
b 7 AAANysAAJAAAARIAAB AAANytAAJAAAAWoAAA
SQL> -- Entry in the MV log gone
SQL> select * from mlog$_t2_a;
no rows selected
SQL> select * from mlog$_t3_a;
no rows selected
SQL>
SQL> -- Insert a row that should affect the MV
SQL> insert into t3_a values('w',6);
1 row created.
SQL> select * from mlog$_t3_a;
C1 SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$
-- -------------------- ---------- ---------- ----------------
M_ROW$$
--------------------------------------------------------------------------------
w 010100 00:00:00 I N FE
AAANytAAJAAAAWoAAC
SQL> alter session set sql_trace = true;
Session altered.
SQL> commit;
Commit complete.
SQL> alter session set sql_trace = false;
Session altered.
SQL> -- MV changed
SQL> select * from mv_t1_a_j_t2_a;
C1 C2 T2ROWID T3ROWID
-- ---------- ------------------ ------------------
b 7 AAANysAAJAAAARIAAB AAANytAAJAAAAWoAAA
w 6 AAANysAAJAAAARIAAC AAANytAAJAAAAWoAAC
SQL> -- Entry in the MV log gone
SQL> select * from mlog$_t3_a;
no rows selected
SQL> select REFRESH_MODE,REFRESH_METHOD,LAST_REFRESH_TYPE, LAST_REFRESH_DATE
2 from user_mviews where mview_name='MV_T1_A_J_T2_A';
REFRES REFRESH_ LAST_REF LAST_REFRESH_DA
------ -------- -------- ---------------
COMMIT FAST FAST 260703 09:35:01
SQL>
SQL> -- Update a row that should chnage the MV
SQL> update t3_a set c2=0 where c1='w';
1 row updated.
SQL> select * from mlog$_t3_a;
C1 SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$
-- -------------------- ---------- ---------- ----------------
M_ROW$$
--------------------------------------------------------------------------------
w 010100 00:00:00 U U 04
AAANytAAJAAAAWoAAC
w 010100 00:00:00 U N 04
AAANytAAJAAAAWoAAC
SQL> commit;
Commit complete.
SQL> -- MV changed
SQL> select * from mv_t1_a_j_t2_a;
C1 C2 T2ROWID T3ROWID
-- ---------- ------------------ ------------------
b 7 AAANysAAJAAAARIAAB AAANytAAJAAAAWoAAA
w 0 AAANysAAJAAAARIAAC AAANytAAJAAAAWoAAC
SQL> -- Entry in the MV log gone
SQL> select * from mlog$_t3_a;
no rows selected
SQL>
SQL>
SQL> spool off