Guess I wasn't too clear. Here's the scoop (there is probably a much easier
way to do this, but I just can't get there from here). There is a 10million
row table on a remote database. Each week, there is a change in about
5-10,000 rows (inserts). Currently this table is brought over to a local
database. A convoluted query is then run to keep our developers happy and
the result set is entered into another table. The query, though convoluted,
does not use any aggregate functions, just a mess of joins. Right now this
query is run on the complete 10 million row table brought over from the
remote database. What I would like to do is to run the query on just the new
rows, appending this result to the existing data. Since the snapshot log
used for a fast refresh already keeps track of this for me I thought it
might be a nice way to go. (I guess I could always flip a flag field, but
why do extra work if Oracle already does it for me?)
So my plan was to create a procedure which will manually take a fast
refresh, run the query, check for success, truncate the table underlying the
snapshot view. Since the underlying structure is just a table, and the
master log data transfer is strictly in one direction, it looks like it
should work, but are there any gothchas? It seems to be OK on a test system.
Thanks again.
Henry
-----Original Message-----
Sent: Sunday, September 16, 2001 9:38 AM
To: [EMAIL PROTECTED]; Henry Poras
Henry,
I'm not sure what you're trying to accomplish by
truncating the snapshot. The whole point of the
refresh process is to keep the snapshot in sync with
the master site.
Presumably you are doing a CTAS off of the snapshot to
do your manipulations, so what is the point in
truncating the snapshot? I don't have a db handy to
test whether Oracle detects that the snapshot has been
truncated and does a complete refresh, but if the
snapshot is very large it would probably be faster to
drop and recreate it than to do a complete refresh.
A more detailed explanation of the process would help.
HTH,
-- Anita
--- Henry Poras <[EMAIL PROTECTED]> wrote:
> !! Please do not post Off Topic to this List !!
>
> I need to move the changes in a table from one
> database to another. It
> seemed that fast snapshots would be a good way to do
> this as the database
> will automatically keep track of the changes for me.
> The question is that
> once I move the data to the snapshot side, I need to
> manipulate it, move it
> into another table, and start the cycle again. This
> means truncating the
> snapshot between refreshes. I tried this on a test
> system and it seesm to
> work, but I don't think Oracle supports it. I don't
> want updatable snapshots
> as this is strictly one way movement of data. Has
> anyone else tried this?
> Are there any potential problems? I think I am just
> truncating a table
> underlying the snapshot view so I don't see what
> could go wrong.
>
> Thanks.
>
> Henry
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Henry Poras
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
--------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
__________________________________________________
Terrorist Attacks on U.S. - How can you help?
Donate cash, emergency relief information
http://dailynews.yahoo.com/fc/US/Emergency_Information/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Henry Poras
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
