Spatial Error Related with SDO_NN

SELECT sdo_nn_distance (1) dist, a.cid,

sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 1) x,

sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 2) y

FROM code_boundaries a, golf_boundaries b

WHERE sdo_nn (a.sdo_geometry, b.sdo_geometry, 'sdo_num_res=1 UNIT=foot ', 1) = 'TRUE'

AND b.gid = 1341

When executing this sql  following error occurs .

ORA-13249: SDO_NN cannot be evaluated without using index

ORA-06512: at "MDSYS.MD", line 1723

ORA-06512: at "MDSYS.MDERR", line 17

ORA-06512: at "MDSYS.PRVT_IDX", line 9

We can resolve this error by removing  SDO_NN_DISTANCE ancillary operator from the query but what to do when we require distance what I have noted   is that it is due to non-spatial column which is in where clause is not indexed .When you create a index for that non-spatial column the query will execute

SQL > create index idx on golf_boundaries(gid) ;

SELECT sdo_nn_distance (1) dist, a.cid,

sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 1) x,

sdo_geom.sdo_min_mbr_ordinate (b.sdo_geometry, 2) y
FROM code_boundaries a, golf_boundaries b

WHERE sdo_nn (a.sdo_geometry, b.sdo_geometry, 'sdo_num_res=1 UNIT=foot ', 1) = 'TRUE'

AND b.gid = 1341 ;

cool

-------