11 Replies Latest reply: Jun 6, 2013 9:34 AM by Stefan Jager RSS

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

    467820
      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
          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
            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
              Ying Hu-Oracle
              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
                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
                  # 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
                    Ying Hu-Oracle
                    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
                      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
                        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
                          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
                            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
                              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