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.

These 2 links helped. community.oracle.com/message/11059758#11059758 and docs.oracle.com/cd/E11882_01/appdev.112/e11830/… — Weishi Z
– Weishi Z, Commented Feb 18, 2016 at 1:50

Albert Godfrind · Accepted Answer · 2013-10-18 14:33:01Z

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';

answered Oct 18, 2013 at 14:33

Albert Godfrind

2,0781 gold badge13 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Albert Godfrind Over a year ago

SDO_NN works fine, but you need to understand how it works to use it correctly. It is implemented by matching the RTREE indexes on the two tables involved. Which implies that the optimizer must pass that index to the SDO_JOIN operator. That will be the case for things like: "give me the 5 closest restaurants to my location". But if you say: "give me the 5 closest CHINESE restaurants to my location" and there is an index on the restaurant type, it is possible that the optimizer decides to use this type index instead of the spatial index.

Albert Godfrind Over a year ago

This can be the case if the restaurant type is very rare (like maybe a TIBETAN restaurant). To be safe, you then need to add a hint to encourage the optimizer to use the spatial index instead.

Albert Godfrind Over a year ago

Another issue with SDO_NN is in the above example: the default approach is to (1) get the 5 closest restaurants, then (2) return only the ones of the selected type. And the answer may be: there are none. That is because none of the five nearest are Tibetan. There is a Tibetan restaurant: but it is some distance away, maybe the 50th nearest.

Albert Godfrind Over a year ago

For that there is a different syntax of SDO_NN where you do not specify any limit: the search continues processing the restaurants in order of proximity, and only retaining the ones that match all the conditions (type, open today, takes credit cards, serves vegetarian food ...). To limit the result to the N closest, just add a ROWNUM filter.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related