Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: truncating snapshots

RE: truncating snapshots

From: Henry Poras <>
Date: Mon, 17 Sep 2001 09:20:50 -0700
Message-ID: <>

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.


-----Original Message-----
Sent: Sunday, September 16, 2001 9:38 AM To:; Henry Poras


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.


Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information
Please see the official ORACLE-L FAQ:
Author: Henry Poras

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: (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).
Received on Mon Sep 17 2001 - 11:20:50 CDT

Original text of this message