Bad performance using st_geometry functions in oracle

[Ulises]

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]

Try flipping the order of your parameters.

- V

[VinceAngelo]

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 on

SELECT 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

[MarcoBoeringa]

I gave up on

SELECT 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]

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