SQL & PL/SQL

Hi my query below is performing very slowly, many many hours, it used to take 90 mins ish:

insert into INCIDENTS_REGION (region_id, incident_id, table_id, input_date, incubating, version, type)
select r.id as region_id, i.id as incident_id, i.table_id, i.input_date, i.incubating, 1 as version,
r.type
from regions r, incidents i, incidents_geom ig
where
mdsys.sdo_relate(ig.geom, r.geometry, 'MASK=ANYINTERACT QUERYTYPE=WINDOW ') = 'TRUE' and
i.id = ig.id
and r.type = 42
and i.incubating = 1
and i.table_id = 1461

All incidents eg crimes are loaded into the Incident table which has x and y columns, when this is done the x and y values are used to create a point object in the Incident_Geom table via a trigger.

There is also a regions table and type = 42 means postcodes, there are 244912 of them.

the spatial query above basically works out which postcodes the incidents occurred in.

I have an index on the type column in the regions tables
and a sequential index on the incubating,table_id columns in the incident table

I also have spatial indexes like so:

CREATE INDEX REGIONS_SX ON REGIONS (GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX
CREATE INDEX INCIDENTS_GEOM_SX ON INCIDENTS_GEOM (GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX

When i run the explain plan the indexes are not used. Here is the execution plan which i ran for one incident i.e. by specifying an id = xx, it takes too long to do all

Execution Plan
----------------------------------------------------------
Plan hash value: 4042171246

--------------------------------------------------------------------------------
-------------------

| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |

--------------------------------------------------------------------------------
-------------------

| 0 | INSERT STATEMENT | | 153 | 12393 | 67
1 (1)| 00:00:09 |

| 1 | NESTED LOOPS | | 153 | 12393 | 67
1 (1)| 00:00:09 |

| 2 | NESTED LOOPS | | 1 | 45 |
5 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| INCIDENTS_GEOM | 1 | 24 |
3 (0)| 00:00:01 |

|* 4 | INDEX UNIQUE SCAN | INCIDENTS_GEOM_PK | 1 | |
2 (0)| 00:00:01 |

|* 5 | TABLE ACCESS BY INDEX ROWID| INCIDENTS | 1 | 21 |
2 (0)| 00:00:01 |

|* 6 | INDEX UNIQUE SCAN | INCIDENTS_PK | 1 | |
1 (0)| 00:00:01 |

|* 7 | TABLE ACCESS FULL | REGIONS | 153 | 5508 | 66
6 (1)| 00:00:08 |

--------------------------------------------------------------------------------


Any ideas guys? Your help would be fantastic

Edited by: user13063002 on 30-Apr-2010 08:34

Edited by: user13063002 on 30-Apr-2010 08:47