Oracle Database Discussions

PLEASE IGNORE THIS DUPLICATE$ - POSTED TWICE DUE TO PROBLEM WITH INTERNET CONNECTION MY END, SORRY!!

Hi,

I have a question concerning materialized views. I have just started playing with them in our environment and something strikes me as slightly odd.

Environment is:

Oracle 10g v10.2.0.4 Standard
Windows 2003 Standard Edition

I will now demonstrate setting up the simple test case.

First I create a simple table with two columns, primary key on column1:

user1@db1 >  create table mv_test_tab (col1 number, col2 number);
user1@db1 >  alter table mv_test_tab add constraint mv_test_tab_pk primary key (col1);
user1@db1 >  insert into mv_test_tab values (1,2);
user1@db1 >  insert into mv_test_tab values (2,3);
user1@db1 >  commit;

Next I create a materialized view log (recording the primary key), and a materialized view (fash refresh, on commit) which just does a select of col1 of the table:

user1@db1 >  create materialized view log
2  on mv_test_tab
3  with primary key;

user1@db1 >  create materialized view mv_test_mv
2  refresh fast on commit  as
3  select col1 from mv_test_tab;

user1@db1 >  select * from user_mview_logs ;

LOG_OWNER               MASTER                   LOG_TABLE                LOG_TRIGGER             ROW PRI OBJ FIL SEQ INC
----------------------- ------------------------ ------------------------ ----------------------- --- --- --- --- --- ---
ADAM                    MV_TEST_TAB              MLOG$_MV_TEST_TAB                                NO  YES NO  NO  NO  NO

user1@db1 >  select MVIEW_NAME, QUERY, LAST_REFRESH_DATE from user_mviews;

MVIEW_NAME                     QUERY                          LAST_REFRESH_DATE
------------------------------ ------------------------------ -------------------
MV_TEST_MV                     select col1 from mv_test_tab   19/08/2009 10:52:19

Now, if I make a change to the master table involving col1, I expect the materialized view to refresh, and it does:

user1@db1 >  insert into mv_test_tab values (3,6);

1 fila creada.

user1@db1 >  select * from MLOG$_MV_TEST_TAB ;

      COL1 SNAPTIME$$          D O CHANGE_VECTOR$$
---------- ------------------- - - ------------------------------
         3 01/01/4000 00:00:00 I N FE

user1@db1 >  commit ;

Confirmación terminada.

user1@db1 >  select * from MLOG$_MV_TEST_TAB ;

ninguna fila seleccionada

user1@db1 >  select * from MV_TEST_MV       ;

      COL1
----------
         1
         2
         3

user1@db1 >  select MVIEW_NAME, QUERY, LAST_REFRESH_DATE from user_mviews;

MVIEW_NAME                     QUERY                          LAST_REFRESH_DATE
------------------------------ ------------------------------ -------------------
MV_TEST_MV                     select col1 from mv_test_tab   19/08/2009 10:56:08

But when I update the table to change only the value of col2, the materialized view is refreshed again - and I was hoping it wouldn't be!

user1@db1 >  update mv_test_tab set col2 = 15 where col1=1;

1 fila actualizada.

user1@db1 >  select * from  MLOG$_MV_TEST_TAB              ;

      COL1 SNAPTIME$$          D O CHANGE_VECTOR$$
---------- ------------------- - - ------------------------------
         1 01/01/4000 00:00:00 U U 04

user1@db1 >  commit ;

Confirmación terminada.

user1@db1 >  select MVIEW_NAME, QUERY, LAST_REFRESH_DATE from user_mviews;

MVIEW_NAME                     QUERY                          LAST_REFRESH_DATE
------------------------------ ------------------------------ -------------------
MV_TEST_MV                     select col1 from mv_test_tab   19/08/2009 10:56:38

I was just hoping someone would be able to explain why this is, and if there's a way to avoid it happening, or maybe even if it behaves differently in a different (later?) version of Oracle. I would hope to have the view only refresh if the data it is actually reflecting (in this case, col1) was changed - that is, if we change only "other" data in the master table (in this case, col2), the materialized view wouldn't have to do a refresh.


Regards,

Ados

Edited by: Ados on 19-ago-2009 11:07