sdo_nn cannot be evaluated without using index when put inside subquery

2924149

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!

Paul Dziemiela

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#SPATL1032

Its 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

Paul Dziemiela

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#SPATL1032

Its 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

2924149

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