Database Software

I have a Spatial database containing regions in the UK in BNG. It was running originally on an XE database, but was recently ported to Oracle Enterprise 11GR2 using the exp/imp tools.

The function of the database is to perform intersection queries between the regions in the tables (which are all polygons or multipolygons), and shapes drawn by users (which can be points, linestrings, or polygons).

Since moving the database, intersection queries have become unusably slow. This is especially the case with polygon intersection queries. A query that was taking 1.5 seconds on the old XE box is now taking about 45 seconds.

The problem seems to be specifically with R2. R1is unaffected. Also, it's only this database that has the problem. Others, which were originally on 10G Enterprise, are fine.

The original query was simply:

SELECT ut.ID
FROM MYTABLE ut
WHERE SDO_ANYINTERACT(UT.GEOLOC, SDO_GEOM.SDO_BUFFER(userGeom),n_buffer,0.05, 'unit = m')) = 'TRUE';

I found that removing the buffer substantially improved the situation with points and linestrings, but not polygons. I also tried using SDO_RELATE and SDO_WITHIN_DISTANCE (to perform the buffering function). Again, polygons are unusably slow.

I've tried various things like re-creating the spatial indices, but can't seem to get any further. I'm guessing there is something about the query or the data that R2 does not like, or that the problem is somehow related to its origins as an XE database, but don't know where to look.

Edited by: user13293928 on 15-Mar-2011 20:36

Edited by: user13293928 on 15-Mar-2011 22:26