Re: Cursor Over a DBLink

marcoandres7_at_tutopia.com wrote:
> Hi,
> I'm having trouble with a routine to get all rows from a remote table
> using a cursor and a DBLink.
>
> The cursor is defined like:
> CURSOR param IS SELECT * FROM table_at_dblink
> And the operation is like follows:
>
> FOR row IN param LOOP
> UPDATE table t1 SET t1.desc=row.desc WHERE t1.key=row.key
> IF param%NOTFOUND THEN
> INSERT INTO table VALUES (row.key, row.desc)
> END IF
> END LOOP
>
> The process was simplified.
> The problem is that it takes too long for the 14,000 rows, and other
> tables involved with this trouble have 250,000 rows...
>
> I 've keep on working, so I had the partial solution:
>
> CURSOR param IS SELECT * FROM temptable_at_dblink
>
> INSERT INTO temptable SELECT * FROM table_at_dblink
> FOR row IN param LOOP
> UPDATE temptable t1 SET t1.desc=row.desc WHERE t1.key=row.key
> IF param%NOTFOUND THEN
> INSERT INTO table VALUES (row.key, row.desc)
> END IF
> END LOOP
> DELETE FROM temptable
>
> This operation is a lot faster, the main difference is that the cursor
> is on a local table, instead the remote in the previous case.
>
> But we need this operation on several tables, and creating one
> "temptable" for all, or many "temptables"s it tsn't a good solution.
>
> What is the problem with the cursor?, and how can we fixed it?
>
> Thanks in advance,
>
>
> Marco

For what I can see in your code (well the first example), it looks like you are attempting to merge the contents of the DBLink table with a local table. Have you looked into doing this as a single SQL statement (using merge) as opposed to row by row? You may find it is a lot faster:-

<UNTESTED>

merge into localtable t
using ( select desc, key

        from table_at_dblink ) b
on ( t.key = b.key )
when matched then
  update t set t.desc = b.desc
when not matched then
  insert ( t.key, t.desc )
  values ( b.key, b.desc );

</UNTESTED>

Also, if there are other columns on the DBLink table, then don't do select *, as it will bring a lot of needless data back across the network. Received on Tue Sep 13 2005 - 09:44:35 CDT