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

jhking <jhking_at_airmail.net> wrote in message 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.

Can you CTAS on the remote site to make a table of what you want, select it into a file, ftp over and sqlload? That's not actually changing anything, but does require more than select privs. What you gain is minimizing sqlnet interaction with the frame relay, ftp can be repeated until it works. Even better if you can talk them into an sp that you just have to fire off, that might be more PC if they feel they have control over sp's.

Sometimes the way to deal with dumb old versions is with dumb old tools.

jg

--
@home.com is bogus.
http://www.technologyreview.com/blog/blog.asp?byCat=1