Forum Stats

  • 3,873,464 Users
  • 2,266,576 Discussions
  • 7,911,547 Comments

Discussions

sdo_nn cannot be evaluated without using index when put inside subquery

2924149
2924149 Member Posts: 2
edited Apr 6, 2015 3:49AM in Spatial Discussions

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!

29241492791715

Best Answer

  • Paul Dziemiela
    Paul Dziemiela Member Posts: 534 Silver Badge
    edited Apr 4, 2015 12:14PM 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#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

    29241492924149

Answers

  • Paul Dziemiela
    Paul Dziemiela Member Posts: 534 Silver Badge
    edited Apr 4, 2015 12:14PM 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#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

    29241492924149
  • 2924149
    2924149 Member Posts: 2
    edited Apr 6, 2015 3:49AM

    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

This discussion has been closed.