Re: Materialized View Log

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 16 Apr 2003 13:16:27 -0700
Message-ID: <130ba93a.0304161216.57a49d47@posting.google.com>

In case Richard still did not convince you... I think you might be confusing "FAST" with "ON COMMIT". "FAST" refers to the "incremental" refreshing mechanism, while "ON COMMIT" has to do with the "timing" of the refresh. Just because your trigger on the MV fires whenever you commit on the master table, does not mean the refresh is "FAST" or "incremental". Most likely you don't have MV log, but I don't believe your MV is created with the "REFRESH FAST" option. Here is a little demo:

SQL> create table test5 as select * from test1;

Table created.

SQL> alter table test5 add constraint test5_pk primary key(c1);

Table altered.

Immediately get error if MV log does not exist SQL> create materialized view test5_mv 2 refresh fast on commit 3 as select * from test5; as select * from test5 * ERROR at line 3: ORA-23413: table "JTEST"."TEST5" does not have a materialized view log

SQL> create materialized view test5_mv
2 refresh on commit
3 as select * from test5;

Materialized view created.

SQL>
SQL> create or replace trigger test5_trg   2 after insert or update or delete
  3 on test5_mv
  4 for each row
  5 begin
  6 if inserting then
  7 insert into test5_log values('insert');   8 elsif deleting then
  9 insert into test5_log values('delete'); 10 else
11 insert into test5_log values('update'); 12 end if;
13 end;
14 /

Trigger created.

SQL> truncate table test5_log;

Table truncated.

SQL>
SQL> select * from test5;

C1 C2 C3

---------- -- --------------------
         1 a  row1
         2 b  test1 row2

SQL> select * from test5_mv;

C1 C2 C3

---------- -- --------------------
         1 a  row1
         2 b  test1 row2

SQL> select * from test5_log;

no rows selected

SQL> update test5 set c3='row2' where c1=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from test5;

C1 C2 C3

---------- -- --------------------
         1 a  row2
         2 b  test1 row2

SQL> select * from test5_mv;

C1 C2 C3

---------- -- --------------------
         1 a  row2
         2 b  test1 row2

Entire MV is deleted, and reloaded from master table SQL> select * from test5_log;

LOGSTR

delete
delete
insert
insert

USER_MVIEWS indicates complete refresh SQL> select last_refresh_date, last_refresh_type from user_mviews where mview_name='TEST5_MV';

LAST_REFRESH_DA LAST_REF
--------------- --------
160403 13:11:29 COMPLETE SQL>

Jusung Yang

"Timothy Madsen" <timothy_madsen_at_bigfoot.com> wrote in message news:<b7jj3m$cup$1_at_bob.news.rcn.net>...
> Hello,
> Well - that is pretty much why I am asking...
> :)
>
> I have done rather extensive testing and have implemented a solution to a
> particular problem which required a trigger to be operating on the COMMIT
> operation. The triggers on the MV tables are now firing when the COMMIT
> occurs. I specifically did not execute any creation script for the MV log -
> and I have checked with both Quest Schema Manager and Quest SQL Navigator to
> see if the MV logs were somehow created automagically for me - behind the
> scenes - I do not see them.
>
> So, I am pretty confident (although as always - I could be wrong) that:
> 1) My triggers on my MV tables are firing during the COMMIT and
> 2) I have no materialized log.
>
> Note that I started using these MV from an example from Thomas Kyte
> (asktom.oracle.com) which did not include the creation of a materialized
> log.
>
> So, I was wondering of what purpose the MV logs are and under what
> circumstances one is required to use them?
>
> Thanks.
> Tim.
>
> "Jusung Yang" <JusungYang_at_yahoo.com> wrote in message
> news:130ba93a.0304151602.3822d81e_at_posting.google.com...
> > "Timothy Madsen" <timothy_madsen_at_bigfoot.com> wrote in message
> news:<b7hjm3$j4v$1_at_bob.news.rcn.net>...
> > > Hello,
> > > I am not clear on why/when one needs to use a 'materialized view log'.
> > >
> > > Currently, in my Oracle 8.1.7.3.0 database, I have created a few
> > > materialized views (refresh fast on commit) - simply for the purpose of
> > > having a 'commit trigger'.
> > >
> > > It seems to work fine without having the materialized view log being
> > > created - so I was wondering under what circumstances the log is
> required
> > > and what it does?
> > >
> > > Thanks.
> > > Tim.
> >
> > Are you sure? Without a materialized view log, you should not be able
> > to create the MV with "REFRESH FAST" - ON COMMIT or otherwise. MV log
> > is needed if you wish to fast refresh your MVs.
> >
> >
> > - Jusung Yang
Received on Wed Apr 16 2003 - 15:16:27 CDT

Re: Materialized View Log - ??