ANALYSIS AND OBSERVATION
It has been observed that a table join over a database link, i.e. a join between a local and remote
database tables, is very costly and drastically affects the application performance. Please find below
for the representative existing code and the optimized code for this scenario.
EXISTING CODE
SELECT P.PATIENT_LNAME, P.PATIENT_FNAME, H.HOSPITAL_NAME, C.CLAIM_ID,
C.CLAIM_NAME, C.CLAIM_DATE, CP.CLAIM_PROVIDER_NAME
FROM PATIENT P, HOSPITAL H,
CLAIM@REMOTE_DB C, CLAIM_PROVIDER@REMOTE_DB CP
WHERE P.PATIENT_ID = C.PATIENT_ID
AND P.PATIENT_ID = H.PATIENT_ID
AND H.ADMIT_DATE = C.CLAIM_DATE
AND C.PATIENT_ID = 1000
AND C.CLAIM_DATE = SYSDATE
AND C.CLAIM_PROVIDER_ID = CP.CLAIM_PROVIDER_ID;
In the existing scenario, the local database (LOCAL_DB) tables, namely PATIENT and HOSPITAL,
were joined with the Remote database (REMOTE_DB) tables CLAIM and CLAIM_PROVIDER and it
was taking around 297 seconds for a single execution.
OPTIMIZATION TECHNIQUE IMPLEMENTED
The joins over the database link was removed by rewriting the logic using temp tables and procedural
code wherever possible.
OPTIMIZED CODE
In the optimized scenario, the existing code is broken into two sections. In the first section, the data is
fetched from the remote database and inserted into the global temporary table (GTT) of the local
database. In the second section, the GTT is joined with the other tables locally. The combined timings
of both SQLs from sections were taking only 22 seconds. Please find below for the section i and ii SQL
code:
i. INSERT INTO CLAIM_GTT_TEMP@LOCAL_DB
(PATIENT_ID, CLAIM_ID, CLAIM_NAME, CLAIM_DATE, CLAIM_PROVIDER_NAME)
SELECT C.PATIENT_ID, C.CLAIM_ID, C.CLAIM_NAME, C.CLAIM_DATE,
CP.CLAIM_PROVIDER_NAME
FROM CLAIM C, CLAIM_PROVIDER CP
WHERE C.PATIENT_ID = 1000
AND C.CLAIM_DATE = SYSDATE
AND C.CLAIM_PROVIDER_ID = CP.CLAIM_PROVIDER_ID;
ii. SELECT P.PATIENT_LNAME, P.PATIENT_FNAME,
H.HOSPITAL_NAME, CGT.CLAIM_ID, CGT.CLAIM_NAME,
CGT.CLAIM_DATE, CGT.CLAIM_PROVIDER_NAME
FROM PATIENT P, HOSPITAL H,
CLAIM_GTT_TEMP CGT