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 |