Bad performance using st_geometry functions in oracle

Question

We have a 10.2 geodatabase running in an Oracle 11.2.0.7 AIX server.  I'm testing running some simple st functions to see how it works, but the performance so far is really bad.  The query tested is...

select t.designatio,p.catastro
from GIS.puntos_control t ,GIS.parcelas_evw p 
where sde.st_intersects (t.shape,p.shape)=1
and rownum<5;

puntos_control is a point feature (198 features) and parcelas is a polygon feature (1,381,141 features).  I want the designation number of each point and the parcel number of each polygon it intersects with.  I added rownum<5 to have a quick response, but it took 3952.163 seconds to fetched just 4 rows.  In the database I can see a lot of non-database CPU time and the wait event "External procedure call".  Tried the same thing in a test PostGIS database with no geodatabase and it took a few seconds for all the points.  Any suggestions are welcome.

Thanks

VinceAngelo · Answer

I stand by the "differently" designation.  I don't know enough about Oracle 
optimizer hints, but I imagine you could change the query text to make the
incorrect order function perform adequately.

Esri has no control over what is available in the developer libraries for database 
extensions.

- V

MarcoBoeringa · Answer

I gave up onSELECT a.polyid, p.pointid
FROM f_pnts p, f_pols a
WHERE SDE.st_intersects(p.point,a.poly)=1;

after ten minutes.  When I dropped the polygon count by an order of magnitude,
this "find points which overlap polys" query ran in 14.56 minutes.

...
Note:  The PostgreSQL optimizer works differently, so the equivalent query on 
SDE.ST_GEOMETRY in PG 9.1 returned in <45ms against 216k polygons, 
no matter the order, and completed in <50ms against 1.34m polygons.

Looking at these figures, wouldn't it be better to say the Oracle optimizer isn't working at all for this particular case of the order of the datasets input to the the SDE.st_intersects command?, whereas the PostgreSQL optimizer, correctly considers optimal order for processing the command?

Is this an Oracle issue, or does ESRI need to adjust the ST_GEOMETRY implementation for Oracle, to have the Oracle optimizer optimally process the command?

VinceAngelo · Answer

I simulated your dataset with 200 points and 1.5m polygons, and successfully queried:SELECT a.polyid, p.pointid
FROM f_pnts p, f_pols a
WHERE SDE.st_intersects(a.poly,p.point)=1;

in  847.96 ms. I gave up onSELECT a.polyid, p.pointid
FROM f_pnts p, f_pols a
WHERE SDE.st_intersects(p.point,a.poly)=1;

after ten minutes.  When I dropped the polygon count by an order of magnitude,
this "find points which overlap polys" query ran in 14.56 minutes.

So the moral of this story is to make sure you place the smaller set of geometries
 in the second position in the Oracle SDE.ST_INTERSECTS operator.

Note:  The PostgreSQL optimizer works differently, so the equivalent query on 
SDE.ST_GEOMETRY in PG 9.1 returned in <45ms against 216k polygons, 
no matter the order, and completed in <50ms against 1.34m polygons.

- V