SQL, remote queries and joins
Date: Fri, 18 Sep 1998 11:11:10 +0200
Message-ID: <360223AE.3B39_at_hospvd.ch>
Hi,
I have the following question:
We have two ORACLE 7.3 databases on the same DataGeneral Unix server.
One
(called PROD) is the production one, and the other one (TEST) is used to
simulate all the important operations before they are done to PROD.
Although it is quite large, it is a small subset of PROD.
I recently tried to simulate a modified insert/select on the TEST
database.
As all the necessary datas were not present on TEST, I created a link
with
PROD and modified my query so that the datas were collected on the PROD
base.
The INSERT/SELECT looks like this one:
INSERT into T_FONDS_12
select -- /*+ INDEX(OCMVC OCMVC_ecr) */
numocecr, typocecr, decocecr, dcrocecr, pieocecr,
libocecr, budocmvc, mtcocmvc, mtdocmvc
FROM T_FONDS_11, OCMVC_at_PROD
WHERE ecrocmvc = numocecr and etsocmvc = '01' and budocmvc is not null;
COMMIT;
This operation proved very slow and did finally fail. The error message
stated that there was not enough space to add a segment in the TEMP
tablespace:
- TEMP is around 100 MB
- The select SHOULD return around 27000 entries from the OCMVC table.
- The OCMVC table is large (around 600 Mb and 3800000 rows)
- The other table T_FONDS_11 is a lot smaller (around 6Mb and 63000 rows)
I interepreted this behavior as the transfer of a large part of OCMVC from PROD to the TEST instance, which doesn't have enough room to store it. Is this right?
In order to complete my test, I copied the table T_FONDS_11 from TEST to PROD, created an ad-hoc table with the same structure than OCMVC, but only the rows that the SELECT should retrieve. Then, I copied the ad-hoc table from PROD to TEST and executed my test once again, that was done quickly and without any more trouble.
Now, my question is the following: in the case of a join including
tables
of different instance, what is done on each instance and what is
tranferred
between instances? Il looked both at the ORACLE DBA Handbook and at the
ORACLE PL/SQL Programming guide (both from ORACLE Press) and I did not
find
a precise answer to this question.
Second question, is there a way to determine (and control) where given
parts
of a query will be executed?
Finally, is there a written documentation for this question? If it is
the
case, what is its reference?
Any help will be appreciated
Emmanuel Baechler
LES HOSPICES CANTONAUX
Office Informatique
Bugnon 21
1005 Lausanne
Received on Fri Sep 18 1998 - 11:11:10 CEST