Re: suggestions for poor-man's datawarehouse performance problem

jhking <jhking_at_airmail.net> wrote in news:cd7ald$gk2_at_library1.airnews.net:

> Environment Host: Windows 2k, Oracle 9.0.2.5. Remote Sites Windows 2k
> Oracle 8.1.7.
> Connection over somewhat flakey frame relay system.
> My objective is to gather new/updated data from each remote site and
> aggregate it on the host. The first issue is that for political reasons
> I can't change anything on the remote sites (which kills replication or
> triggers + queues). So I create dblinks to the remote sites and drive
> the updates from the host. My first answer, do a insert into .. select
> from for each table I need into staging tables and then move from the
> staging tables into aggregate tables. For the sites where the
> connection stays up this is fine, but if the connection dies during the
> insert into .. select from the session hangs. I've done google and
> metalink research to find a solution to that but AFAICT that's not
> solvable. My second answer is to use cursors and fetch a row at a time.
> Disconnects result in raised errors (which I can deal with) but the
> performance has become abysmal. I did some tests with bulk fetch and it
> seems to behave like insert into .. select from hanging on disconnects.
> I can do virtually anything to the host box and virtually nothing to the
> remote boxes. Any suggestions on how to make insert into .. select from
> work in this fragile environment or alternative approaches I could
> take to getting the data aggregated would be welcome.
>

Change Data Capture feature in 9i Received on Thu Jul 15 2004 - 21:06:21 CDT