I have the following table:
CREATE TABLE MY_DATA (
MY_DATA_ID NUMBER(38,0) PRIMARY KEY,
GEOM SDO_GEOMETRY
);
I've set its SDO metadata and created a spatial index:
INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, SRID, DIMINFO)
VALUES ('MY_DATA', 'GEOM', 4326, MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',-180.0000,180.0000,0.00000000001),MDSYS.SDO_DIM_ELEMENT('Y',-90.0000,90,0.00000000001)));
CREATE INDEX SPIX_MY_DATA ON MY_DATA (GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
The table has about 150,000 rows.
I need to find rows where the geometries intersect one specific row. This seems like a simple enough task. All it takes is a JOIN
and a WHERE
clause:
SELECT /*+ INDEX(MD2 SPIX_MY_DATA)*/
MD2.MY_DATA_ID
FROM MY_DATA MD1
JOIN MY_DATA MD2 ON SDO_RELATE(MD1.GEOM, MD2.GEOM, 'mask=ANYINTERACT') = 'TRUE'
WHERE MD1.MY_DATA_ID = 143668 AND MD2.MY_DATA_ID != 143668
But this query is very slow. It takes over 85 seconds to fetch only 8 related rows. We can see from the query plan that it's not using the spatial index, in spite of the index hint:
Plan hash value: 4008712617
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 379 | 9854 | 1390 (1)| 00:00:17 |
| 1 | NESTED LOOPS | | 379 | 9854 | 1390 (1)| 00:00:17 |
| 2 | TABLE ACCESS BY INDEX ROWID| MY_DATA | 1 | 13 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | SYS_C00755898 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | MY_DATA | 379 | 4927 | 1388 (1)| 00:00:17 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("MD1"."MY_DATA_ID"=143668)
4 - filter("MD2"."MY_DATA_ID"<>143668 AND
"MDSYS"."SDO_RTREE_RELATE"("MD1"."GEOM","MD2"."GEOM",'mask=ANYINTERACT
querytype=window ')='TRUE')
(Row 2 is MD1, and row 4 is MD2.)
(I tried changing the hint to /*+ INDEX(MY_DATA SPIX_MY_DATA)*/
, but this also had no effect. I've made sure my statistics are up to date.
How can I speed up this query? How can I get it to use the spatial index?
I'm currently using Oracle 11.2.