| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Materialized View Log - ??
Hello,
I misspoke in my original post - I am actually not doing a "refresh fast on
commit" - rather I am doing a "refresh on commit". Perhaps this makes the
difference in regards to the MV log? From the post by Richard it sounds
like maybe the MV is being entirely rebuild each time it is refreshed
(because I am not using the keywork FAST) - as opposed to only the
incremental changes being used to refresh the MV from the MV log (ie -
fast). Would this be correct?
What I wanted was a "commit trigger" - which of course does not exist in Oracle (at least 8.1.7.3.0...)
So, I saw a suggestion from Tom Kyte to use a MV (using Refresh on Commit) and to put a trigger on the MV. This trigger now operates as a "commit trigger" - for the particular table involved.
For reference, see the discussion which I used as a reference at: http://asktom.oracle.com/pls/ask/f?p=4950:8:51862823933951580::NO::F4950_P8_ DISPLAYID,F4950_P8_CRITERIA:2986214404007, I did as Tom suggested - and it worked great for me.
Now - just trying to further my understanding of MV and MV logs - I am wondering about the purpose of the MV log.
This is the script I use to create my MV: CREATE MATERIALIZED VIEW mv_widgets_order
REFRESH ON COMMIT
AS
SELECT order_id,
COUNT(*) as cnt
FROM inserter_order
GROUP BY order_id;
and my trigger:
CREATE OR REPLACE TRIGGER ts_mv_widgets_order
AFTER INSERT OR UPDATE OR DELETE
ON mv_widgets_order
BEGIN
Production.dequeueWidgetOrderIds;
END;
/
Thanks.
Tim.
"Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:o7dna.15725$1s1.250399_at_newsfeeds.bigpond.com...
> "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?
> >
> Hi Tim,
>
>
>
>
>
>
 |
 |