SDO_NN cannot be evaluated without using index when using inside in statement

Question

If I run the following query:

select B3.bid as id ,B3.bshape as shape 
from Buildings B3 
where B3.bid in 
(
   select distinct B1.bid from Buildings B1, 
   (
     select * from Buildings B where B.bname in (select BOF.bname from Buildings_On_Fire BOF)
   ) B2 where sdo_nn(B1.bshape, B2.bshape, 'distance=100') = 'TRUE' and B1.bname != b2.bname
)

I receive following errors:

ERROR at line 1:
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

However if just run the following subquery:

select distinct B1.bid from Buildings B1, 
(
   select * from Buildings B  where B.bname in (select BOF.bname from Buildings_On_Fire BOF)
) B2 where sdo_nn(B1.bshape, B2.bshape, 'distance=100') = 'TRUE' and B1.bname != b2.bname

This executed fine. I have verified the spatial index, they seems to be valid.
I am new to oracle and have no idea what to do next. please help.

If there is solution which doesn't require changing the above query, that would be best.

Answer 1

A bit late for an answer, but here comes ...

The error you get is because the optimizer did not use the spatial index to solve the SDO_NN operator. Contrary to the other spatial operators (SDO_RELATE, SDO_WIHIN_DISTANCE), SDO_NN cannot be resolved without the help of the index.

Then again I suspect your query is incorrectly formulated. If I understand correctly, what you want to do is find all buildings that are within a distance of 100 (what ? meters ?) from any building that is on fire. For that, use the SDO_WITHIN_DISTANCE operator.

Let's assume your tables are like this:

buildings (bid number, bname varchar2(30), bshape sdo_geometry)

buildings_on_fire (bid number, bname varchar2(30))

The select will then be like this:

select b1.bid as id, b1.bshape as shape
from   buildings b1, 
       buildings b2, 
       buildings_on_fire bof
where  b2.bname = bof.bname
and    b1.bname <> b2.bname
and    sdo_within_distance (
         b1.bshape, b2.bshape, 
         'distance=100 unit=meter'
       ) = 'TRUE';