Forum Stats

  • 3,872,036 Users
  • 2,266,368 Discussions
  • 7,911,033 Comments

Discussions

mdsys.sdo_relate

770327
770327 Member Posts: 2
edited May 4, 2010 3:07AM in 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
Tagged:

Answers

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    What version of Oracle are you on?
    select * from v$version;
    Also, can you post the results from the following query?
    select 
       ig.geom, 
       r.geometry, 
       count(*)
    from  
       regions r, 
       incidents i, 
       incidents_geom ig
    where i.id           = ig.id
    and   r.type         = 42
    and   i.incubating   = 1
    and   i.table_id     = 1461 
    group by ig.geom, r.geometry
    order by count(*) asc;
    And .... do you have any triggers defined on the target of your insert statement (INCIDENTS_REGION)?
    Tubby
  • 770327
    770327 Member Posts: 2
    Its version:

    10.2.0.4.0

    there are no triggers on the IncidentsRegion insert

    Here is the plan for the select query, i did a select * because it didnt like comparing a nested table of varray or lob attributes of an object:
    Any ideas on why the spatial index isnt being used on the regions table?

    SQL> select
    2 *
    3 from
    4 BUCKSADMIN_TEST.regions r,
    5 BUCKSADMIN_TEST.incidents i,
    6 BUCKSADMIN_TEST.incidents_geom ig
    7 where i.id = ig.id
    8 and r.type = 42
    9 and i.incubating = 1
    10 and i.table_id = 1461
    11 order by count(*) asc;

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 639618164

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

    | Id | Operation | Name | Rows | Bytes | C
    ost (%CPU)| Time |

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

    | 0 | SELECT STATEMENT | | 83175 | 9340K|
    3333 (1)| 00:00:40 |

    | 1 | MERGE JOIN CARTESIAN | | 83175 | 9340K|
    3333 (1)| 00:00:40 |

    | 2 | NESTED LOOPS | | 5 | 395 |
    14 (0)| 00:00:01 |

    | 3 | TABLE ACCESS BY INDEX ROWID| INCIDENTS | 5 | 275 |
    4 (0)| 00:00:01 |

    |* 4 | INDEX RANGE SCAN | INCIDENTS_UPDATE_IDX | 5 | |
    3 (0)| 00:00:01 |

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

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

    | 7 | BUFFER SORT | | 15153 | 532K|
    3331 (1)| 00:00:40 |

    |* 8 | TABLE ACCESS FULL | REGIONS | 15153 | 532K|
    664 (1)| 00:00:08 |

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


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

    4 - access("I"."INCUBATING"=1 AND "I"."TABLE_ID"=1461)
    6 - access("I"."ID"="IG"."ID")
    8 - filter("R"."TYPE"=42)
This discussion has been closed.