RE: truncating snapshots

From: Henry Poras <Henry.Poras_at_ctp.com>
Date: Thu, 20 Sep 2001 10:26:33 -0700
Message-ID: <F001.00393EAA.20010920104129@fatcity.com>

Shailesh,
Thanks. My thoughts on #2 are that if the snapshot refresh and the delete from the master log comprise a single transaction (2 phase commit), then I shouldn't have a problem. A full refresh might be necessary at some point, but I could deal with that with some specific, infrequently used code. Of course I still need to confirm that this is a single transaction. Not quite sure how to do this. Maybe try a trace, or limit available space on snapshot so inserts will error. I'll see what happens.

Henry

-----Original Message-----
Sent: Thursday, September 20, 2001 1:56 PM To: Multiple recipients of list ORACLE-L

Henry,

  I think Anita's concerns are valid however in my experience of doing the refreshes like you plan to do I have not encountered the problem# 2. For problem# 1 what we do here is check the table you are going to populate the changes into for existance of that primary key. If found then update the record with new values else insert into the table.   The real catch here would be the deletions. Try to get the deletes before you do the refresh from the MLOG$xxx at the master site and then use those primary keys to perform the deletion.
  We have about 100 snapshots that are used to perform the aforementioned task for over a year without any problems. Let me know if you have any problem.

HTH,
Shailesh

-----Original Message-----
Sent: Tuesday, September 18, 2001 11:52 AM To: Multiple recipients of list ORACLE-L

Henry,

OK, I think I see what you're trying to accomplish. By truncating the snapshot between refreshes, the refresh is essentially populating the snapshot with only the changed rows from the master site.

Probably not supported, but I can't see that it would cause any real problems. The way the refresh mechanism works, the missing rows on the snapshot site shouldn't be a problem.

I see a couple of potential problems, however.

1. The refresh will pull over all changed rows on the master site, in your case inserts AND updates. So if a row does get updated on the master site, then it will get refreshed to the snapshot site and your counts will be off since you'll be treating the update of an existing row as a newly inserted row.

It all depends on how critical the numbers are for the developers, because I can assure you it's only a matter of time before someone updates rows on a table that should only have inserts ;)

2. If a fast refresh fails this requires that the next refresh is a complete refresh, or the snapshot is recreated, so you will not have a way of getting just the set of changed rows. Your procedure will need to be able to detect this and perform the joins against the entire table again.

In the long run you're probably much better off developing your own trigger to populate another table or setting a flag, as you mentioned. Just because it works today doesn't mean that it will work in a newer release if they change the refresh mechanism.

HTH,

• Anita
  • Henry Poras <Henry.Poras_at_ctp.com> wrote:
    > 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: ORACLE-L_at_fatcity.com; 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 <Henry.Poras_at_ctp.com> 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
    

    ---

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: A. Bardeen
  INET: abardeen1_at_yahoo.com

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: ListGuru_at_fatcity.com (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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Yadav, Shailesh
  INET: NDASY3_at_labor.state.ny.us

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: ListGuru_at_fatcity.com (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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Henry Poras
  INET: Henry.Poras_at_ctp.com

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: ListGuru_at_fatcity.com (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 Thu Sep 20 2001 - 12:26:33 CDT