ORA-13249: SDO_NN cannot be evaluated without using index on 12.1.0.2 (Doc ID 2391158.1)

Last updated on APRIL 01, 2020

Applies to:

Symptoms

The database was migrated to Amazon Relational Database Service (RDS) – AWS, with Oracle Locator enabled, from Enterprise Edition to Standard Edition on 12.1.0.2. Since then, getting an error while executing the below query:

SELECT COUNT(distinct mark_id)
FROM V_SMES_SEARCH_GENERAL
WHERE STATUS = 'OK'
AND ( SDO_RELATE(SHAPE ,SDO_GEOM.SDO_BUFFER(SDO_CS.TRANSFORM(SDO_GEOMETRY('POLYGON ((2496224.89244844 2409463.33836873, 2496200.22985964 2409530.64437753, 2496193.37475972 2409549.35373003, 2496159.97650483 2409537.51057725, 2496191.5457019 2409451.18126673, 2496224.89244844 2409463.33836873))', 82473),4283), 500, 1,'unit=m arc_tolerance=0.05'),'mask=ANYINTERACT') = 'TRUE'
AND SDO_NN(SHAPE, SDO_GEOMETRY('POLYGON ((2496224.89244844 2409463.33836873, 2496200.22985964 2409530.64437753, 2496193.37475972 2409549.35373003, 2496159.97650483 2409537.51057725, 2496191.5457019 2409451.18126673, 2496224.89244844 2409463.33836873))', 82473), 'sdo_num_res=250 unit=m', 1) = 'TRUE' )
ORDER BY sdo_nn_distance (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
13249. 00000 - "%s"
*Cause: An internal error was encountered in the extensible spatial index
  component. The text of the message is obtained from some
  other server component.
*Action: Contact Oracle Support Services with the exact error text.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.