Re: Query with dblink runs slow

Makbo wrote:

> Try using the "DRIVING_SITE" hint to the CBO. It can make a huge
> difference. This is documented somewhere in Oracle's chapters on
> distributed databases.

Yes. Part of the problem however is that this hint will be ignored if any part of the remote query needs to be resolved locally.

Consider something like this:
SELECT
  /*+ DRIVING_SITE INDEX(f,foo_col1_idx) */   *
FROM foo_at_remotedb f
WHERE col1 = localfunction/localsubselect

Oracle does not resolve the local part first in the where clause and then send the resulting query across to the remote db (i.e. it will not perform value substition in this case).

Instead, it reverts to a full table scan - pulling the _entire_ remote table across row by row, and process that locally.

To get the DRIVING_SITE hint to work, and make the remote query use the remote indexes, you need to ensure that the entire query can be run as a stand-alone query on the remote db (i.e. log into the remote db via sqlplus and run the query as is).

--
Billy