db_links and optimiser problem?

From: Jonathan Waland <jonathan.waland_at_vf.vodafone.co.uk>
Date: Thu, 08 Oct 1998 08:54:18 +0100
Message-ID: <361C6FAA.39B88D3A@vf.vodafone.co.uk>

[posted on behalf of a collegue]

I am having serious response problems when attempting to combine local and remote database tables in one SQL query. Obviously this in itself is not the problem, something else is the cause but what????

SELECT remote.x, remote.y, remote.z
FROM remote_at_db1
WHERE 1 = 1; Works correctly

SELECT remote.x, remote.y, remote.z, sysdate FROM remote_at_db1
WHERE 1 = 1; Works correctly

SELECT remote.x, remote.y, remote.z
FROM remote_at_db1, dual
WHERE 1 = 1; Dies a horrible death

SELECT remote.x, remote.y, remote.z, sysdate FROM remote_at_db1, dual
WHERE 1 = 1; Also dead

I've used dual as an example but really I can use any local database, also the sql is pretty basic, the problem really came about when using in-line views, I've just stripped away the code that is not causing any problem.

The remote db table has an index that gets used correctly when dual is not included however it appears not to be used when dual is included, even after adding in hints.

The remote db table is quite large (2GB). The sql, when it runs!! only returns 70 rows and takes about 1 sec cpu which is why I want to use it in a in-line view rather than joining on the whole table.

The other point to note is that the local db is 8.0.3, the remote is 7.3.3.

This is the explain of the query without a local db in the from clause:

Execution Plan

---

          0
SELECT STATEMENT (REMOTE) Optimizer=CHOOSE (Cost=141 Card=4543 Bytes=236236)

          1                  0

  TABLE ACCESS (BY ROWID) OF 'ROUTE_DAILY' (Cost=141 Card=4543 Bytes=236236)
GPD.WORLD

          2                  1

    INDEX (RANGE SCAN) OF 'RDAILY_PK' (UNIQUE) GPD.WORLD This is the explain of the same sql with a local db in the where clause:

Execution Plan

---

          0
SELECT STATEMENT Optimizer=RULE

          1                  0

  NESTED LOOPS

          2                  1

    TABLE ACCESS (FULL) OF 'DUAL'

          3                  1

    FILTER

          4                  3
      REMOTE*

GPD.WORLD           4
SERIAL_FROM_REMOTE
SELECT "SAMPLE_DATE","SOURCE","ROUTE","SUM_CONG" FROM "RD" "RD" WHERE "SUM_CONG" Wierd!!!

Can any help?

Thanks.

rgds,

Jon Waland
Vodafone Ltd. Received on Thu Oct 08 1998 - 02:54:18 CDT