Using Select in from clause involving Remote connections.
The query below is using a remote link namely SBUP. The tables
involved contain Gigabytes of information. it is taking forever
to return since the inner query doing a full table scan.
Is there any alternate way to do this query.
SELECT DISTINCT REGISTER_DATE,POL_NO FROM (
SELECT DISTINCT CURRENT_POL.REGISTER_DATE,
CURRENT_POL.POL_NO
FROM CURRENT_POLICY@SBUP CURRENT_POL,CUST_PART@SBUP
CUST_PART,PTY_KEY_MAP@SBUP KEY_MAP
WHERE KEY_MAP.NEW_PTY_ID = CUST_PART.PTY_ID AND
CUST_PART.POL_NO
= CURRENT_POL.POL_NO AND CUST_PART.CUST_PART_END_DT
IS NULL
AND INSTR('+136688330+','+' || KEY_MAP.PSNID || '+' )
!= 0
ORDER BY CURRENT_POL.REGISTER_DATE,CURRENT_POL.POL_NO
) WHERE ROWNUM <= 6