Re: Remote cursors (cursors across DB links)

Instead of copying your remote tables, read the documentation on MATERIALIZED VIEWs. You can create a MATERIALIZED VIEW of a remote table that is automatically updated whenever the base table is. So all DML at the remote site will be mirrored at your site. Then you won't have to copy the tables. Copying the tables probably accounts for most of your 13 minutes.

Best!

Randall

mlm <not_at_listed.com> wrote in message news:3B13BEC2.14C7B824_at_listed.com...
> I need some advice regarding a PL/SQL procedure that creates a cursor
> against a table that is in another database. We have a DB link to the
> remote database, and I create this cursor using the @ sign to signify
> that the table is a remote table. Our site has a T1 connection to this
> remote database, but the procedure runs very slowly. The procedure runs
> for hours and I kill it before it completes.
>
> My solution was to create a temporary table in the local database, copy
> all of the remote tables contents to this temp table and create the
> cursor against this temp table. This method cuts the execution time of
> the proc to ~13 minutes. There are about 90K records in this table.
>
> Are there any other ways to accomplish this task? I would prefer not to
> use this solution, if there was some way to make the remote cursor run
> faster. Has anyone run into problems like this before? Can anyone
> point me to any resources on the net that talk about this issue? I've
> looked all over and can't find anything that references my problem.
>
> Thanks,
> Mike
>
> --
> The views expressed here are mine and do not represent Unisys
> Corporation in any way.
>
>
Received on Tue May 29 2001 - 11:45:48 CDT