distributed databases

From: Robin McEntire <robin_at_prc.unisys.com>
Date: Wed, 16 Sep 1992 14:17:27 GMT
Message-ID: <1992Sep16.141727.10506_at_gvl.unisys.com>


I'm working with a number of databases some of which access tables in other of the databases through database links. We have found that a number of our queries when executed against these remote tables execute very slowly, in fact something on the order of a magnitude worse.

I played with a query that performs joins across three tables. Each of these tables exists in a remote database and is therefore accessible only through database links. All of these three tables live in the same remote database. When I looked at the execution plan for this query in the local database I found that the local database was dividing this query into three separate queries, each to be executed remotely in the *same* database. Of course, this means that the joins are done locally rather than remotely. The problem, not surprisingly, is that one of the tables happens to be large, and so the remote database essentially ships the appropriate columns for every row in the entire table over to the local database. And this appears to be where we are taking a tremendous hit in execution time.

It's hard to believe that the execution plan for this remote query would not look for or notice that all three subqueries belong to the same remote database, so that the entire query could/should be executed remotely.

Is it really the case that this is the way Oracle wants to execute this query or is there something that I should be doing to Oracle so it will do the right thing?

thanks,
  robin

-- 

--------------------------------------------------------------------------------
Robin McEntire | Internet: robin_at_prc.unisys.com Paramax Systems |
Valley Forge Labs Research and Development |
--------------------------------------------------------------------------------
Received on Wed Sep 16 1992 - 16:17:27 CEST

Original text of this message