FW: Fast Refresh Materialized View on Prebuilt Table

From: iggy_fernandez_at_hotmail.com
To: sethmiller.sm_at_gmail.com
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_at_gmail.com
To: iggy_fernandez_at_hotmail.com
CC: dmarc-noreply_at_freelists.org; kibeha_at_gmail.com; oracle-l_at_freelists.org

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_at_hotmail.com> 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_at_freelists.org
To: kibeha_at_gmail.com; oracle-l_at_freelists.org 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_at_gmail.com> 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_at_gmail.com _at_kibeha On Mon, Oct 6, 2014 at 12:25 PM, Charlotte Hammond <dmarc-noreply_at_freelists.org> 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                                                      

--
http://www.freelists.org/webpage/oracle-l