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.