SDO_ANYINTERACT query runs slower with numerous iterations
407973Dec 17 2008 — edited Dec 24 2008hi all,
I have some PL/SQL code within a loop that take longer and longer to run as it iterates through the loop.
I have identified the problem function below. It seems that the SDO_ANYINTERACT takes longer and longer to execute the more it is called.
I have found a bug on metalink 7003151 with indicates a potential memory leak issue. Could this be a cause? I know that this function runs as expected using Oracle Express. The issue is on a development server which has been patched to 10.2.0.4.
FUNCTION SEARCHFORFEATURE(sTABLE VARCHAR2,gGEOM MDSYS.SDO_GEOMETRY, nSEARCH NUMBER) RETURN VARCHAR2 IS
TYPE typNIMSREF IS TABLE OF VARCHAR2(10);
vNIMSREF TYPNIMSREF;
sSQL VARCHAR2(500);
BEGIN
sSQL := 'SELECT NIMSREF FROM ' || sTABLE || ' S WHERE SDO_ANYINTERACT(S.GEOLOC, :gGEOM) = 'TRUE';
EXECUTE IMMEDIATE sSQL BULK COLLECT INTO vNIMSref USING gGEOM;
IF vNIMSREF.COUNT = 1 THEN
RETURN vNIMSREF(1);
ELSIF vNIMSREF.COUNT > 1 THEN
RETURN '-1';
ELSE
RETURN '-2';
END IF;
EXCEPTION -- exception-handling part starts here
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line(sSQL);
END SEARCHFORFEATURE;
Thanks in advance
Daniel