Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

sdo_nn cannot be evaluated without using index when put inside subquery

2924149Apr 3 2015 — edited Apr 6 2015

Hello everyone,

I have met a problem when using sdo_nn function to find nearest neighbor. Below is my scenario:

_ I have 2 tables client and store.

_ Client table has client_ID and a sdo_geom of 2D point

_ Store table has store_ID and a sdo_geom of 2D polygon.

Initially, I have this query to find nearest store to each client as below:

select s.STORE_ID, c.CLIENT_ID

      from store s, client c

      where sdo_nn(s.MYPOLYGON, c.MYPOINT, 'sdo_num_res=1', 1) = 'TRUE';

_It works as expected where it returns a table showing the nearest store to each client.

_Now I want to count how many clients who have the same nearest store:

select /*+ INDEX (store store_spatial_idx, client client_spatial_idx)*/ count(nearest_store.CLIENT_ID)
from (select s.STORE_ID, c.CLIENT_ID
      from store s, client c
      where sdo_nn(s.MYPOLYGON, c.MYPOINT, 'sdo_num_res=1', 1) = 'TRUE') nearest_store
group by nearest_store.STORE_ID;

Executing this query produces the following error:

Error report -
SQL Error: 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
13249. 00000 -  "%s"

I'm pretty new to spatial database and hope to get some help to go further. Thank you in advance!

This post has been answered by Paul Dziemiela on Apr 4 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 4 2015
Added on Apr 3 2015
2 comments
6,520 views