Re: Fast Refresh Materialized View on Prebuilt Table
- From: "Charlotte Hammond" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "charlottejanehammond@xxxxxxxxx" for DMARC)
- To: Kim Berg Hansen <kibeha@xxxxxxxxx>
- Date: Mon, 6 Oct 2014 17:26:17 +0000 (UTC)
Thanks Kim - that looks interesting. I'll have to give a try.
In the meantime I've got the following working :-
1. Create trigger on base table to write primary key of any DML to a log
table, dml_log (a sort of DIY materialized view log in addition to the real MV
Log).2. Copy table to remove site and create materialized view. Set up fast
refresh schedule.3. Run a "fake" update using my dml_log table as a driver
(UPDATE base_table SET any_old_column=any_old_column WHERE pk IN (SELECT pk
FROM dml_log)). This does not update any data but causes the relevant rows to
be re-added to the MV Log. The next fast refresh picks these up and
inserts/updates them as needed at the target.4. Drop trigger and dml_log
Charlotte
On Monday, October 6, 2014 3:39 PM, Kim Berg Hansen <kibeha@xxxxxxxxx>
wrote:
I haven't tried this, but perhaps there might be some hints you can use in the
section of the replication manual called "Performing an Offline Instantiation
of a Materialized View Site Using Export/Import" :
http://docs.oracle.com/cd/B28359_01/server.111/b28327/rarmanmv.htm#i30332
Looks rather complex, I'm afraid...
But you said your issue was that the mview log on the source is deleted when
you create your mview on the target?
Perhaps experiment with something like:
- create mview on target without prebuilt table but with BUILD DEFERRED and
a next_date far in the future
- get the metadata on the source database with "select * from
dba_registered_mviews"
- drop the mview on the target
- register mview manually on the source with DBMS_MVIEW.REGISTER_MVIEW using
the metadata found in 2)
- copy the table
- create the mview using PREBUILT TABLE
Maybe if you are lucky it will not clear the mview log on the source since the
mview was registered in the source BEFORE the create mview is executed.I am not
certain it will work, but there is a chance that registering the mview on the
source just possibly can make the source consider that the mview is from before
the copy and therefore still needs the mview log entries from the intervening
hours.Worth a try, maybe?
Regards
Kim Berg Hansen
http://dspsd.blogspot.comkibeha@xxxxxxxxx@kibeha
On Mon, Oct 6, 2014 at 4:00 PM, Charlotte Hammond
<charlottejanehammond@xxxxxxxxx> wrote:
Hi Kim,
Unfortunately the table is too big and the network too slow/flaky for a
complete refresh. It's ok for the relatively small daily updates through a
fast refresh, but a complete refresh requires shipping the table data on
physical media.
Charlotte
On Monday, October 6, 2014 11:57 AM, Kim Berg Hansen <kibeha@xxxxxxxxx>
wrote:
Hi Charlotte
Is there a specific reason you wish to copy the table from source to target
manually and then create the mview using prebuilt?
We use mview replication quite a bit, and I've always just created the fast
refreshable mviews with "build immediate" and that way let the mview do the
initial copying of the table.That way the source mview log keeps the changes
during the mview build and then the first fast refresh afterwards will get
those changes.
Regards
Kim Berg Hansen
http://dspsd.blogspot.comkibeha@xxxxxxxxx@kibeha
On Mon, Oct 6, 2014 at 12:25 PM, Charlotte Hammond
<dmarc-noreply@xxxxxxxxxxxxx> wrote:
Hi All,
I'm creating a large fast-refreshable materialized view using a prebuilt table.
The problem I have is that by the time I have copied the table from the source
system to the target system several hours have passed and there are now
numerous changes on the target. As soon as I issue the CREATE MATERIALIZED
VIEW command the log is deleted and the fast refresh starts on a table which is
by now out of date.
I'm looking for a good way to sync up the changes made during the copy period.
I've got some ideas using triggers (and possibly a small outage on the source)
but it's seems messy. I'm wondering if I'm missing some clean / built-in
functionality to resolve this?
The database is 11.2.0.4 SE
Thanks in advance for any ideas!Charlotte
Other related posts:
