Re: Last Row Inserted

From: at\ <_at_>
Date: Mon, 05 Sep 2005 11:56:01 +0200
Message-ID: <1125914162.f5c10879b8c582daa7b4a4a34788e269@teranews>

at"@ wrote:
> DA Morgan wrote:
>
>

>>In a single schema?
>>In a single database?
>>Across databases?
>>For disaster recovery?
>>For a reporting data mart?
>>Just to waste disk space?  ;-)

>
>
> I wasn't going to get into the details too much as my original question
> refered to a SQL solution, but since I'm considering replication now:
>
> Table 1 is accessed via a database link. It is a production server and
> I don't want to do anything other than read from it.
>
> Table 2 is on a new box that I'm building for research purposes which
> contains data similar to Table 1 but lots more (Time/Sources etc).
>
> So Table 2 is Table 1 with more data, hence, I need to use a separate
> sequence to generate new rows in Table 2. I can't see Table 2 being a
> materialized view and I don't mind so much about updates/deletes because
> I never ever manipulate data in Table 1 after insert as a rule.
>
> Thanks,
>
> Kevin

More info:
Table 1 is quite large, 10M+ rows with CLOBS (actually there are lots of tables in a single schema, but most can be updated via a primary key).  It needs to be sync'd quite frequently (1/minute).

I am currently replicating it as an exact clone using a min/max index search on the primary key on table 2 then inserting as a select from remote db for greater than primary key. This is very fast, i.e. 2 seconds for the whole schema.

I am on phase 2 of the build of my research box, where I will be adding rows from other sources (realtime applications) to table 2, hence, I need a separate sequence.

My idea until replication was suggested was to keep a bookkeeping table where I stored the last known primary key for table 2, inserted everything greater than that, and then updated my bookkeeping table, hence I need to know the last primary key inserted by the bulk insert. I realize this is probably reinventing the wheel, but I'm not sure if replication will allow me to insert from the other sources.

Perhaps someone can clarify things for me or tell me if I'm just going about it the wrong way.

Thanks,

Kevin Received on Mon Sep 05 2005 - 04:56:01 CDT