This discussion is archived
11 Replies Latest reply: Jun 6, 2013 7:34 AM by Stefan Jager RSS

SDO_NN giving ORA-13249: SDO_NN cannot be evaluated without using index

467820 Newbie
Currently Being Moderated
Hi people,

I do not understand why the SDO_NN gives ORA-13249 under circumstances.

SQL> SELECT s.title FROM SlavaTest s WHERE SDO_NN(s.geometry, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(14.0, 49.0, NULL), null, null)) = 'TRUE' and title like '%' and rownum < 10;

TITLE
--------------------------------------------------------------------------------
MultiPoint_305199
LineString_691779
MultiPolygon_180478
MultiPolygon_358113
MultiPolygon_53008
MultiPolygon_249905
MultiPolygon_204076
MultiPolygon_636994
MultiPoint_464514

9 rows selected.

SQL> SELECT s.title FROM SlavaTest s WHERE SDO_NN(s.geometry, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(14.0, 49.0, NULL), null, null)) = 'TRUE' and timestamp >= to_timestamp('01/06/2011', 'dd/mm/yyyy') and rownum < 10;
SELECT s.title FROM SlavaTest s WHERE SDO_NN(s.geometry, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(14.0, 49.0, NULL), null, null)) = 'TRUE' and timestamp >= to_timestamp('01/06/2011', 'dd/mm/yyyy') and rownum < 10
*
ERROR at line 1:
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 49

The spatial index is created with:
CREATE INDEX SlavaTest_geometry_idx_spatial ON SlavaTest(geometry) INDEXTYPE IS mdsys.spatial_index;

Both "title" and "timestamp" columns have an index either.

Note the query is issued by Hibernate and I cannot change it arbitrary.
  • 1. Re: SDO_NN giving ORA-13249: SDO_NN cannot be evaluated without using index
    user8048037 Newbie
    Currently Being Moderated
    Try to give ORACLE the INDEX hint explicitely:
    SELECT /*+ INDEX (s SlavaTest_geometry_idx_spatia) */  
               s.title
      FROM slavatest s
     WHERE     sdo_nn (s.geometry,
                       sdo_geometry (2001,
                                     4326,
                                     sdo_point_type (14.0, 49.0, NULL),
                                     NULL,
                                     NULL)) = 'TRUE'
           AND timestamp >= TO_TIMESTAMP ('01/06/2011', 'dd/mm/yyyy')
           AND ROWNUM < 10;
  • 2. Re: SDO_NN giving ORA-13249: SDO_NN cannot be evaluated without using index
    467820 Newbie
    Currently Being Moderated
    Nope:
    # SELECT /*+ INDEX (s SlavaTest_geometry_idx_spatial) */ s.title FROM SlavaTest s WHERE SDO_NN(s.geometry, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(14.0, 49.0, NULL), null, null)) = 'TRUE' and timestamp >= to_timestamp('01/06/2011', 'dd/mm/yyyy') and rownum < 10;
    SELECT /*+ INDEX (s SlavaTest_geometry_idx_spatial) */     s.title FROM SlavaTest s WHERE SDO_NN(s.geometry, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(14.0, 49.0, NULL), null, null)) = 'TRUE' and timestamp >= to_timestamp('01/06/2011', 'dd/mm/yyyy') and rownum < 10
    *
    ERROR at line 1:
    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 49

    Additionally:
    # SELECT s.title FROM SlavaTest s WHERE SDO_NN(s.geometry, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(14.0, 49.0, NULL), null, null)) = 'TRUE' and title like '%p%' and rownum < 10;
    SELECT s.title FROM SlavaTest s WHERE SDO_NN(s.geometry, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(14.0, 49.0, NULL), null, null)) = 'TRUE' and title like '%p%' and rownum < 10
    *
    ERROR at line 1:
    ORA-29903: error in executing ODCIIndexFetch() routine
    ORA-22165: given index [32767] must be in the range of [0] to [32766]
  • 3. Re: SDO_NN giving ORA-13249: SDO_NN cannot be evaluated without using index
    yhu Journeyer
    Currently Being Moderated
    How about the following?
    SELECT s.title FROM SlavaTest s 
    WHERE SDO_NN(s.geometry, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(14.0, 49.0, NULL), null, null), 'sdo_batch_size=10') = 'TRUE' 
         and title like '%p%' and rownum < 10;
  • 4. Re: SDO_NN giving ORA-13249: SDO_NN cannot be evaluated without using index
    user8048037 Newbie
    Currently Being Moderated
    There is a typo in the index name.
    I get in a similar test case:
    DELETE FROM USER_SDO_GEOM_METADATA 
      WHERE TABLE_NAME = 'SLAVATEST' AND COLUMN_NAME = 'GEOMETRY';
    
    INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) 
      VALUES ('SLAVATEST', 'GEOMETRY', 
        MDSYS.SDO_DIM_ARRAY 
          (MDSYS.SDO_DIM_ELEMENT('X', 13.5, 14.5, 0.001), 
           MDSYS.SDO_DIM_ELEMENT('Y', 49.5, 50.5, 0.001)
         ),4326); 
    
    CREATE TABLE slavatest 
     ( title    VARCHAR2(40),
       geometry sdo_geometry);
      
    CREATE INDEX SlavaTest_geometry_idx_spatial 
              ON slavatest(geometry)
       INDEXTYPE IS MDSYS.SPATIAL_INDEX 
    PARAMETERS('sdo_indx_dims=2');
    
    INSERT INTO slavatest (title, geometry)
         VALUES ('t1', sdo_geometry (2001, 4326, sdo_point_type (14.1, 49.1, NULL),NULL,NULL));  
    
    INSERT INTO slavatest (title, geometry)
         VALUES ('t2', sdo_geometry (2001, 4326, sdo_point_type (14.2, 49.1, NULL),NULL,NULL));  
    
    SELECT /*+ INDEX (s SlavaTest_geometry_idx_spatial) */  
           s.title
      FROM slavatest s
     WHERE sdo_nn (s.geometry, sdo_geometry (2001, 4326, sdo_point_type (14.0, 49.0, NULL),NULL,NULL)) = 'TRUE';
    
    TITLE                                   
    ----------------------------------------
    t1                                      
    t2                                      
    
    2 rows selected.
    
    SELECT /*+ NO_INDEX (s SlavaTest_geometry_idx_spatial) */  
           s.title
      FROM slavatest s
     WHERE sdo_nn (s.geometry, sdo_geometry (2001, 4326, sdo_point_type (14.0, 49.0, NULL),NULL,NULL)) = 'TRUE';
    
    ORA-13249: SDO_NN cannot be evaluated without using index
    ORA-06512: in "MDSYS.MD", Zeile 1723
    ORA-06512: in "MDSYS.MDERR", Zeile 17
    ORA-06512: in "MDSYS.PRVT_IDX", Zeile 49
    Can you find out, which index is used in your query ?
    And please format your code, as we do.
  • 5. Re: SDO_NN giving ORA-13249: SDO_NN cannot be evaluated without using index
    467820 Newbie
    Currently Being Moderated
    # SELECT s.title FROM SlavaTest s WHERE SDO_NN(s.geometry, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(14.0, 49.0, NULL), null, null), 'sdo_batch_size=10') = 'TRUE' and title like '%P%' and rownum < 10;
    MultiPoint_305199
    MultiPolygon_180478
    MultiPolygon_358113
    MultiPolygon_53008
    MultiPolygon_249905
    MultiPolygon_204076
    MultiPolygon_636994
    MultiPoint_464514
    MultiPolygon_32800

    9 rows selected.

    But
    # SELECT s.title FROM SlavaTest s WHERE SDO_NN(s.geometry, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(14.0, 49.0, NULL), null, null), 'sdo_batch_size=10') = 'TRUE' and timestamp >= to_timestamp('01/06/2011', 'dd/mm/yyyy') and rownum < 10;
    SELECT s.title FROM SlavaTest s WHERE SDO_NN(s.geometry, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(14.0, 49.0, NULL), null, null), 'sdo_batch_size=10') = 'TRUE' and timestamp >= to_timestamp('01/06/2011', 'dd/mm/yyyy') and rownum < 10
    *
    ERROR at line 1:
    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
  • 6. Re: SDO_NN giving ORA-13249: SDO_NN cannot be evaluated without using index
    yhu Journeyer
    Currently Being Moderated
    You may specify a hint "NO_INDEX (s the_index_on_timestamp)".
  • 7. Re: SDO_NN giving ORA-13249: SDO_NN cannot be evaluated without using index
    467820 Newbie
    Currently Being Moderated
    user8048037 wrote:
    There is a typo in the index name.
    I noticed and corrected
    SELECT /*+ NO_INDEX (s SlavaTest_geometry_idx_spatial) */
    Spatial queries require an index, this is bound to fail and is expected to do so, isn't it?
    Can you find out, which index is used in your query ?
    My last Oracle was severals years ago, and I have to read much to realize how to show what you request. Could you please just say what to issue in sqlplus?
    And please format your code, as we do.
    Will try, sorry ;( Is there a tag for sql code, which does autoformat?
  • 8. Re: SDO_NN giving ORA-13249: SDO_NN cannot be evaluated without using index
    user8048037 Newbie
    Currently Being Moderated
    I suspect Oracle uses the index on the row timestamp as @Yhu wrote.
    Find out the INDEX name and use the NO_INDEX hint as in my example to force Orcale not to use this one, but the INDEX on geometry.
    To format as SQL code use the tag CODE (in square brackets).
  • 9. Re: SDO_NN giving ORA-13249: SDO_NN cannot be evaluated without using index
    467820 Newbie
    Currently Being Moderated
    yhu wrote:
    You may specify a hint "NO_INDEX (s the_index_on_timestamp)".
    Does not seem to help.
    Just to refresh: the query is generated by hibernate, I cannot add hints or change the query arbitrary. My influence on sql is very limited.
    The question I am attempting to solve is whether SDO_NN is so buggy and to be avoided alltogether or whether the query is wrongly built / usage is wrong.
  • 10. Re: SDO_NN giving ORA-13249: SDO_NN cannot be evaluated without using index
    467820 Newbie
    Currently Being Moderated
    Slava2 wrote:
    yhu wrote:
    You may specify a hint "NO_INDEX (s the_index_on_timestamp)".
    Does not seem to help.
    Stop, missed a +
    SELECT /*+ NO_INDEX(s SLAVATESTTIMESTAMPINDEX) */s.title
    FROM SlavaTest s 
    WHERE 
        SDO_NN(s.geometry, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(14.0, 49.0, NULL), null, null), 'sdo_batch_size=10') = 'TRUE'
       and timestamp >= to_timestamp('01/06/2011', 'dd/mm/yyyy')
       and rownum < 10;
    results in 9 rows selected.

    What does it mean - is it a bug in Oracle?

    Edited by: Slava2 on Jun 6, 2013 7:27 AM
  • 11. Re: SDO_NN giving ORA-13249: SDO_NN cannot be evaluated without using index
    Stefan Jager Journeyer
    Currently Being Moderated
    Slava2 wrote:
    What does it mean - is it a bug in Oracle?
    Well, it could probably be considered one, but the [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_operat.htm#i78067]documentation on SDO_NN warns you:
    Documentation says:
    However, if the column predicate in the WHERE clause specifies any nonspatial column in the table for geometry1 that has an associated index, be sure that this index is not used by specifying the NO_INDEX hint for that index.
    Cheers,
    Stefan

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points