Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Processing
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,796 views