Re: Synchronizing 2 tables in same database -how ?

From: Christian Svensson <chse30_at_hotmail.com>
Date: 20 Nov 2003 02:44:01 -0800
Message-ID: <ccc2a7eb.0311200244.68ccecec@posting.google.com>

Hi,

Sorry for the non-background explanation in previous post. See answers below:

>
> To answer your question requires knowing why you would want two
> identical ~60GB tables.
>
> 1. Is the intention that they always remain identical?

No, after a bathjob processing is done(~20h), I want to start a synchronzing job, that hopefully takes less than 20h.

> 2. If not what is the purpose? How will the replicate be used?

Its a datawarehouse that have been running for a while with longer and longer run times. The design of the ETL (PL/SQL) was first intended for Demo only, but it was then built further and further, and now the run times for a daily load is ~18-20 hours...(there are no one to blame, the developers are far gone) But there are a project initiated to replace this with another architecture, but that is another story. So until the new architecture is on place we have to keep the current solution breething as much as possible.

When the ETL batchjob runs (called elc job), the bitmap indexes on the datamart table (called elc) are dropped. When users do drill down in their reports direct down to the elc table, it takes very long times to get the data due to missing bitmap indexes... hence because the elc job is running. Since the elc job works for ~20h there are hardly any "free" time when the users can do drill down.

So we are looking at a solution to have 2 copies of the datamart table, and the drilldowns from reports are accessing a synonym that points to either copy of the datamart table, i.e the elc table.

So basically the idea is:

1, have 2 identical copies of elc table (here named elc_1 and elc_2)
2, the synonym used by drilldown is pointed to elc_2 table
3, the elc job does its work on elc_1 table, it executes for ~20h
4, when elc job finish, the drill down synonym is poited to elc_1

table
5, a replication job is executing to synchronize elc_1 and elc_2 tables
6, when the replication job is finished the synonym is pointed back to elc_2 table
7, now its time for the elc job to work again, also on elc_1 table

The real problem is that the batch job takes very long time and then the bitmap indexes are dropped so the users response time are hurting. So if the users always drill down to a "complete-table-with-indexes" they will never suffer.

> 3. If so ... why are you doing this at all?

See above

> 4. Is this a one-time only replication or will this be done regularly?

It will be done one time per day, after the batch job completes

> 5. If the tables are to remain identical how close to real-time must the
> replication be?

See above.

I hope this can shed some light over my problem.

If you have any ideas how to solve our problem, please give me some hints or so. Guess I am not the only guy encountering this problem.

Thanks !

/Christian Received on Thu Nov 20 2003 - 04:44:01 CST