Skip to main content
deleted 1 character in body
Source Link
jpmc26
  • 1.6k
  • 3
  • 20
  • 38

(I 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.

(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.

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.

Source Link
jpmc26
  • 1.6k
  • 3
  • 20
  • 38

Why won't Oracle use the spatial index when JOINing tables? (Or: Why is my query with a spatial JOIN so slow in Oracle?)

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.