SDO_FILTER Getting error ORA-13226: Interface Not Supported Without a Spatial Index (Doc ID 1608731.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial and Graph - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

When executing an SDO_FILTER query receiving error ORA-13226: interface not supported without a spatial index
The index is valid but the optimizer is not using/finding it
There spatial index is a functional index that receives longitude and latitude as input and returns an SDO_GEOMETRY

SELECT id FROM geo_test
WHERE SDO_FILTER(MAKEPOINT(longitude,latitude),
  MDSYS.SDO_GEOMETRY(2001, 8307,
  MDSYS.SDO_POINT_TYPE(10,10,NULL), NULL, NULL),
  'querytype=window') = 'TRUE';


Output:

ERROR at line 1:
ORA-13226: interface not supported without a spatial index
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 8
ORA-06512: at "MDSYS.SDO_3GL", line 1248

The index is valid but the optimizer is not using/finding it

select table_name, index_name, status, domidx_status, domidx_opstatus
from user_indexes
where ityp_name='SPATIAL_INDEX';

TABLE_NAME INDEX_NAME STATUS DOMIDX_STATU DOMIDX
-------------------- -------------------- -------- ------------ ------
GEO_TEST GEO_SIDX VALID VALID VALID



When tried the query with the hint

SELECT /*+ index(GEO_TEST GOE_SIDX) */ id FROM geo_test
WHERE SDO_FILTER(MAKEPOINT(longitude,latitude),
  MDSYS.SDO_GEOMETRY(2001, 8307,
  MDSYS.SDO_POINT_TYPE(10,10,NULL), NULL, NULL),
  'querytype=window') = 'TRUE';

no rows selected

Output

Execution Plan
----------------------------------------------------------
Plan hash value: 614064353

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 88542 | 1297K| 18862 (1)| 00:03:47 | | |
| 1 | PARTITION RANGE ALL | | 88542 | 1297K| 18862 (1)| 00:03:47 | 1 | 42 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| GEO_TEST | 88542 | 1297K| 18862 (1)| 00:03:47 | 1 | 42 |
|* 3 | DOMAIN INDEX | GEO_SIDX | | | 0 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

 3 - access("MDSYS"."SDO_FILTER"("MIDAS"."MAKEPOINT"("LONGITUDE","LATITUDE"),"
MDSYS"."SDO_GEOMETRY"(2001,8307,"MDSYS"."SDO_POINT_TYPE"(10,10,NULL),NULL,NULL),'querytype=window')='TRUE')

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms