FW: Fast Refresh Materialized View on Prebuilt Table
- From: Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 6 Oct 2014 16:46:26 -0700
Forwarding to the email list.
From: iggy_fernandez@xxxxxxxxxxx
To: sethmiller.sm@xxxxxxxxx
Subject: RE: Fast Refresh Materialized View on Prebuilt Table
Date: Mon, 6 Oct 2014 16:45:46 -0700
Lots of useful deprecated stuff lying around like exp/imp, Statspack, and--my
favorite--partition views. Partition views were deprecated in 8i while
Statspack and exp/imp were deprecated in 10g. But all of them still around and
work well. Oracle was even kind enough to fix Statspack for pluggable
databases. I predict that Streams will continue to be used for a very long time.
Kim Berg Hansen found a very interesting solution using the materialized views
framework but I didn't have time to dig into the details and convince myself
that it would work.
The OP has gone ahead and implemented a simple trigger. There may be some
corner cases that have not been addressed but the OP knows the environment best.
Iggy
Date: Mon, 6 Oct 2014 17:32:08 -0500
Subject: Re: Fast Refresh Materialized View on Prebuilt Table
From: sethmiller.sm@xxxxxxxxx
To: iggy_fernandez@xxxxxxxxxxx
CC: dmarc-noreply@xxxxxxxxxxxxx; kibeha@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
It sounds like replication is a good idea but keep in mind that Streams is
deprecated.
Seth Miller
On Monday, October 6, 2014, Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx> wrote:
Perhaps you can use Streams instead of Materialized Views. Streams will allow
you to apply changes beginning with a specified SCN. Perhaps you can
"downgrade" to a materialized view once the source and destination tables are
in sync.
Iggy
Date: Mon, 6 Oct 2014 14:00:58 +0000
From: dmarc-noreply@xxxxxxxxxxxxx
To: kibeha@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Fast Refresh Materialized View on Prebuilt Table
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 CharlotteIs 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.RegardsKim Berg Hansenhttp://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 SEThanks in advance for
any ideas!Charlotte
Other related posts:
- » FW: Fast Refresh Materialized View on Prebuilt Table - Iggy Fernandez
