Re: distributed databases

From: Ian A. MacGregor <ian_at_tethys.SLAC.Stanford.EDU>
Date: 23 Sep 92 22:37:33 GMT
Message-ID: <Bv1yuL.9D5_at_unixhub.SLAC.Stanford.EDU>


In article <1992Sep16.141727.10506_at_gvl.unisys.com>, robin_at_prc.unisys.com (Robin McEntire) writes:
|>
|> 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 |
|> --------------------------------------------------------------------------------

The current answer to this dilemma is to create a view of the three tables on the remote database. This will ensure only those rows meeting the join condition are sent across the network. Oracle has mentioned improving distributed queries so will be made on the appropriate database, but I do not know if this improvement has been implemented.

                               Ian MacGregor
                               Stanford Linear Accelerator Center
                               Ian_at_slac.Stanford.edu
                               (415) 926-3528
Received on Thu Sep 24 1992 - 00:37:33 CEST

Original text of this message