Re: Remote cursors (cursors across DB links)

> One problem you are probably running into is that no indexes are used when a
> remote table is accessed over a db link..so your select statement is doing a
> full table scan for each row returned...Combine this with the added overhead of
> the link itself, and slow is the normal result..

Not necessarily true. It all depends on the query issued to the remote database. To find the query issued to the remote database, perform an explain plan on the original, local query. When you query the PLAN_TABLE to determine the execution plan, make sure that you query the OTHER column as well (most people forget this one). This column shows the query that will be sent to the remote database. Now take that remote query over to the remote database and run EXPLAIN PLAN there. You may be surprised to find out that it uses an index. After this query runs on the remote host, the results are shipped back. But the index will not be used to facilitate join processing on a remote table with a local table.

You may find that it is quicker to run the query on the remote database. For that, use the DRIVING_SITE hint.

HTH,
Brian

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================