Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.6K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
sdo_nn cannot be evaluated without using index when put inside subquery

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!
Best Answer
-
Hello Flipper,
Oracle Spatial tends to be a rather complex topic with a lot of variables and moving parts. We've chatted on the group about some kind of FAQ or guidelines for helping folks like you submit questions that actually get answers. First of all, you really gotta tell us the version of Oracle you are using. Particularly with issues involving the optimizer, the version down to the exact patch set number is a good idea. Secondly, you took the time to submit the question so I imagine you want a response. If you really want to see that response then providing a working example is one of the most important things you can do. I'm going to do that here for you but in general people on this forum come and go and often sink into lurkitude so if you want to coax them out to help you, providing a working example is key.
DROP TABLE store1 PURGE; CREATE TABLE store1( store_id INTEGER NOT NULL ,shape MDSYS.SDO_GEOMETRY ,PRIMARY KEY(store_id) ); DROP TABLE client2 PURGE; CREATE TABLE client2( client_id INTEGER NOT NULL ,shape MDSYS.SDO_GEOMETRY ,PRIMARY KEY(client_id) ); CREATE OR REPLACE PROCEDURE seeder( p_client_count IN NUMBER ,p_store_count IN NUMBER ) AS sdo_foo MDSYS.SDO_GEOMETRY; int_counter NUMBER; FUNCTION random_point RETURN MDSYS.SDO_GEOMETRY AS num_x1 NUMBER; num_y1 NUMBER; BEGIN num_x1 := dbms_random.value(-179,179); num_y1 := dbms_random.value(-89,89); RETURN MDSYS.SDO_GEOMETRY( 2001 ,8265 ,MDSYS.SDO_POINT_TYPE( num_x1 ,num_y1 ,NULL ) ,NULL ,NULL ); END random_point; BEGIN int_counter := 1; FOR i IN 1 .. p_client_count LOOP -- Create a client point sdo_foo := random_point(); INSERT INTO client2 VALUES ( int_counter ,sdo_foo ); int_counter := int_counter + 1; END LOOP; int_counter := 1; FOR i IN 1 .. p_store_count LOOP -- Create a store polygon of some kind sdo_foo := MDSYS.SDO_GEOM.SDO_ARC_DENSIFY( MDSYS.SDO_GEOM.SDO_BUFFER( random_point() ,5000 ,0.05 ) ,0.05 ,'arc_tolerance=0.05' ); INSERT INTO store1 VALUES ( int_counter ,sdo_foo ); int_counter := int_counter + 1; END LOOP; COMMIT; END seeder; / BEGIN seeder(10000,200); END; / BEGIN INSERT INTO user_sdo_geom_metadata( table_name ,column_name ,diminfo ,srid ) VALUES ( 'STORE1' ,'SHAPE' ,MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180,180,.05),MDSYS.SDO_DIM_ELEMENT('Y',-90,90,.05)) ,8265 ); COMMIT; EXCEPTION WHEN OTHERS THEN NULL; END; / BEGIN INSERT INTO user_sdo_geom_metadata( table_name ,column_name ,diminfo ,srid ) VALUES ( 'CLIENT2' ,'SHAPE' ,MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180,180,.05),MDSYS.SDO_DIM_ELEMENT('Y',-90,90,.05)) ,8265 ); COMMIT; EXCEPTION WHEN OTHERS THEN NULL; END; / CREATE INDEX store1_spx ON store1 (shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX NOPARALLEL; CREATE INDEX client2_spx ON client2 (shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX NOPARALLEL; /* Works as expected */ SELECT s.store_id ,c.client_id ,MDSYS.SDO_NN_DISTANCE(1) FROM store1 s ,client2 c WHERE MDSYS.SDO_NN( s.shape ,c.shape ,'sdo_num_res=1' ,1 ) = 'TRUE'; /* No worky? Works for me */ SELECT ns.store_id ,COUNT(ns.client_id) FROM ( SELECT s.store_id ,c.client_id FROM store1 s ,client2 c WHERE MDSYS.SDO_NN( s.shape ,c.shape ,'sdo_num_res=1' ,1 ) = 'TRUE' ) ns GROUP BY ns.store_id ORDER BY ns.store_id;
So I wrote this on 12c (12.1.0.2.0) and it all works just fine for me. So then I moved back over to 11gR2 (11.2.0.4.0) and sure enough there are your issues. So I guess you are using some flavor of 11g. So at this point we can look at the docs and see that for 11g you often need to specify which table is leading and which is the one with the spatial index to utilize.
http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_operat.htm#SPATL1032Its rather interesting that the 12c optimizer knows what you want when I had to squint myself at your query and play around a bit with the hinting. Note that SDO_NN is sensitive in that the leading table geometry needs to come second in the operator. I did not know that off the top of my head.
SELECT /*+ LEADING(c) INDEX(s store1_spx) */ s.store_id ,c.client_id ,MDSYS.SDO_NN_DISTANCE(1) FROM store1 s ,client2 c WHERE MDSYS.SDO_NN( s.shape ,c.shape ,'sdo_num_res=1' ,1 ) = 'TRUE'; SELECT ns.store_id ,COUNT(ns.client_id) FROM ( SELECT /*+ LEADING(c) INDEX(s store1_spx) */ s.store_id ,c.client_id ,MDSYS.SDO_NN_DISTANCE(1) FROM store1 s ,client2 c WHERE MDSYS.SDO_NN( s.shape ,c.shape ,'sdo_num_res=1' ,1 ) = 'TRUE' ) ns GROUP BY ns.store_id ORDER BY ns.store_id;
So I think that is your answer. Give it a shot and see if that does the trick. Obviously moving to 12c would be helpful for these types of things. It would be interesting to collect more examples of this sort of spatial thing where 12c is the answer. It would also be nice if we could somehow tag this discussion as only applying to 11g and earlier.
Cheers,
Paul
Answers
-
Hello Flipper,
Oracle Spatial tends to be a rather complex topic with a lot of variables and moving parts. We've chatted on the group about some kind of FAQ or guidelines for helping folks like you submit questions that actually get answers. First of all, you really gotta tell us the version of Oracle you are using. Particularly with issues involving the optimizer, the version down to the exact patch set number is a good idea. Secondly, you took the time to submit the question so I imagine you want a response. If you really want to see that response then providing a working example is one of the most important things you can do. I'm going to do that here for you but in general people on this forum come and go and often sink into lurkitude so if you want to coax them out to help you, providing a working example is key.
DROP TABLE store1 PURGE; CREATE TABLE store1( store_id INTEGER NOT NULL ,shape MDSYS.SDO_GEOMETRY ,PRIMARY KEY(store_id) ); DROP TABLE client2 PURGE; CREATE TABLE client2( client_id INTEGER NOT NULL ,shape MDSYS.SDO_GEOMETRY ,PRIMARY KEY(client_id) ); CREATE OR REPLACE PROCEDURE seeder( p_client_count IN NUMBER ,p_store_count IN NUMBER ) AS sdo_foo MDSYS.SDO_GEOMETRY; int_counter NUMBER; FUNCTION random_point RETURN MDSYS.SDO_GEOMETRY AS num_x1 NUMBER; num_y1 NUMBER; BEGIN num_x1 := dbms_random.value(-179,179); num_y1 := dbms_random.value(-89,89); RETURN MDSYS.SDO_GEOMETRY( 2001 ,8265 ,MDSYS.SDO_POINT_TYPE( num_x1 ,num_y1 ,NULL ) ,NULL ,NULL ); END random_point; BEGIN int_counter := 1; FOR i IN 1 .. p_client_count LOOP -- Create a client point sdo_foo := random_point(); INSERT INTO client2 VALUES ( int_counter ,sdo_foo ); int_counter := int_counter + 1; END LOOP; int_counter := 1; FOR i IN 1 .. p_store_count LOOP -- Create a store polygon of some kind sdo_foo := MDSYS.SDO_GEOM.SDO_ARC_DENSIFY( MDSYS.SDO_GEOM.SDO_BUFFER( random_point() ,5000 ,0.05 ) ,0.05 ,'arc_tolerance=0.05' ); INSERT INTO store1 VALUES ( int_counter ,sdo_foo ); int_counter := int_counter + 1; END LOOP; COMMIT; END seeder; / BEGIN seeder(10000,200); END; / BEGIN INSERT INTO user_sdo_geom_metadata( table_name ,column_name ,diminfo ,srid ) VALUES ( 'STORE1' ,'SHAPE' ,MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180,180,.05),MDSYS.SDO_DIM_ELEMENT('Y',-90,90,.05)) ,8265 ); COMMIT; EXCEPTION WHEN OTHERS THEN NULL; END; / BEGIN INSERT INTO user_sdo_geom_metadata( table_name ,column_name ,diminfo ,srid ) VALUES ( 'CLIENT2' ,'SHAPE' ,MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180,180,.05),MDSYS.SDO_DIM_ELEMENT('Y',-90,90,.05)) ,8265 ); COMMIT; EXCEPTION WHEN OTHERS THEN NULL; END; / CREATE INDEX store1_spx ON store1 (shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX NOPARALLEL; CREATE INDEX client2_spx ON client2 (shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX NOPARALLEL; /* Works as expected */ SELECT s.store_id ,c.client_id ,MDSYS.SDO_NN_DISTANCE(1) FROM store1 s ,client2 c WHERE MDSYS.SDO_NN( s.shape ,c.shape ,'sdo_num_res=1' ,1 ) = 'TRUE'; /* No worky? Works for me */ SELECT ns.store_id ,COUNT(ns.client_id) FROM ( SELECT s.store_id ,c.client_id FROM store1 s ,client2 c WHERE MDSYS.SDO_NN( s.shape ,c.shape ,'sdo_num_res=1' ,1 ) = 'TRUE' ) ns GROUP BY ns.store_id ORDER BY ns.store_id;
So I wrote this on 12c (12.1.0.2.0) and it all works just fine for me. So then I moved back over to 11gR2 (11.2.0.4.0) and sure enough there are your issues. So I guess you are using some flavor of 11g. So at this point we can look at the docs and see that for 11g you often need to specify which table is leading and which is the one with the spatial index to utilize.
http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_operat.htm#SPATL1032Its rather interesting that the 12c optimizer knows what you want when I had to squint myself at your query and play around a bit with the hinting. Note that SDO_NN is sensitive in that the leading table geometry needs to come second in the operator. I did not know that off the top of my head.
SELECT /*+ LEADING(c) INDEX(s store1_spx) */ s.store_id ,c.client_id ,MDSYS.SDO_NN_DISTANCE(1) FROM store1 s ,client2 c WHERE MDSYS.SDO_NN( s.shape ,c.shape ,'sdo_num_res=1' ,1 ) = 'TRUE'; SELECT ns.store_id ,COUNT(ns.client_id) FROM ( SELECT /*+ LEADING(c) INDEX(s store1_spx) */ s.store_id ,c.client_id ,MDSYS.SDO_NN_DISTANCE(1) FROM store1 s ,client2 c WHERE MDSYS.SDO_NN( s.shape ,c.shape ,'sdo_num_res=1' ,1 ) = 'TRUE' ) ns GROUP BY ns.store_id ORDER BY ns.store_id;
So I think that is your answer. Give it a shot and see if that does the trick. Obviously moving to 12c would be helpful for these types of things. It would be interesting to collect more examples of this sort of spatial thing where 12c is the answer. It would also be nice if we could somehow tag this discussion as only applying to 11g and earlier.
Cheers,
Paul
-
Thanks a lot Paul. Your response is indeed the best response I have ever received in any forums that I have asked questions. I greatly appreciate all your suggestions and explanations. In future posts, I will ensure that I have a working example followed by my query question.
I verified that the issue I was having is now resolved when I specify the leading table and index of table being utilized. just FYI that the Oracle database version I'm using is 11.2.0.2.0
Thanks again for your time and effort to help me in this matter.
Sincerely,
Chris