Oracle spatial database, how to use SDO_NN_DISTANCE?

Question

I have two tables:Lion(Lion_id(varchar2(5)), position(mdsys.sdo_geometry)) and Pond(Pond_id(varchar2(5)),shape(mdsys.sdo_geometry)). And I have created spatial index on Lion.position and Pond.shape.

I want to find 3 nearest ponds to the lion L2. Nearest neighbors must be ordered from the closest to the furthest one.

Initially, I try the following query:

SELECT P.POND_ID
FROM POND P,LION L
WHERE L.LION_ID='L2' AND
MDSYS.SDO_NN(P.SHAPE,L.POSITION,'SDO_NUM_RES=3')='TRUE';

This query runs successfully. However, when I try another one:

SELECT P.POND_ID,SDO_NN_DISTANCE(1) DIS
FROM POND P,LION L
WHERE L.LION_ID='L2' AND
MDSYS.SDO_NN(P.SHAPE,L.POSITION,'SDO_NUM_RES=3',1)='TRUE'
ORDER BY DIS;

There comes an error:

ORA-13249: SDO_NN cannot be evaluated without using index
ORA-06512: in "MDSYS.MD", line 1723
ORA-06512: in "MDSYS.MDERR", line 17
ORA-06512: in "MDSYS.PRVT_IDX", line 9

How can I solve this problem? I am new to Oracle and this is an assignment from my database course.

Can anyone help me please? Thanks!!

Answer 1

I have found the solution. I need to add a hint.

SELECT /*+ LEADING(L) USE_NL(L P) INDEX(P POND_INDEX)*/
P.POND_ID, SDO_NN_DISTANCE(1) DIS
FROM POND P,LION L
WHERE L.LION_ID='L2' AND
MDSYS.SDO_NN(P.SHAPE,L.POSITION,'SDO_NUM_RES=3',1)='TRUE'
ORDER BY DIS;

Then the error is solved.