In a View I have an intensive use of a DBLink
SELECT DISTINCT ...
FROM
(
SELECT v.FIELD1, MAX(v.FIELD2) AS FIELD2
FROM TABLE2@MYDBLINK vg INNER JOIN TABLE1@MYDBLINK v ON ...
LEFT OUTER JOIN TABLE1@MYDBLINK v1 ON ...
WHERE v1.FIELD3 IS NULL AND ...
GROUP BY v.FIELD3
) SEL1
INNER JOIN TABLE1@MYDBLINK t1 ON ...
INNER JOIN TABLE2@MYDBLINK t2 ON ...
INNER JOIN TABLE3@MYDBLINK t3 ON ...
INNER JOIN TABLE4@MYDBLINK t4 ON ...
INNER JOIN TABLE5@MYDBLINK t5 ON ...
INNER JOIN TABLE6@MYDBLINK t6 ON ...
LEFT OUTER JOIN
( SELECT DISTINCT ... FROM TABLE7@MYDBLINK pk INNER JOIN TABLE8@MYDBLINK k ON ... WHERE ... ) SEL2 ON ...
LEFT OUTER JOIN TABLE9@MYDBLINK t9 ON ...
LEFT OUTER JOIN TABLE10 t10 ON ...
WHERE t10.ID IS NULL
ORDER BY t5.FIELD1
;
When I first execute a 'SELECT * FROM myView' result show up within seconds. When I execute the same statement again (or even when call the SELECT used in the view) results don't even show up after 20+ Minutes.
When I do the SELECT (without '@MYDBLINK') from the View connected to the User the DBLink MYDBLINK points to I get the results within seconds again.
Can this behaviour be caused by the intense use of a DBLink within one SELECT ?
Kind regards,
Chris