I am experimenting some performance problems and error executions when I use ST_GEOMETRY functions in SQL and I wanted to know how many features from my parcel layer intersect a required municipality. The parcel feature class is versioned and there are several versions created in our database. Using sql I wrote the following statement...
SELECT count(*) FROM GISADMIN.parcelario08 p,GISADMIN.municipios m WHERE sde.st_intersects (p.shape,m.shape) = 1 AND m.municipios='VILLALBA' order by p.num_catastro;
this returns 7434 records in about just a few seconds. Using �??select by location�?� in ArcMap, the application returned 7436 selected features. Both queries were done relatively at the same time. I ran "exec sde.version_util.set_current_version('SDE.DEFAULT');" just in case in my latest tries, but still the same results. I figured out I was referencing the business table and not the corresponding versioned view so I modified the query to�?�
SELECT count(*) FROM GISADMIN.parcelario08_evw p,GISADMIN.municipios m WHERE sde.st_intersects (p.shape,m.shape) = 1 AND m.municipios='VILLALBA' order by p.num_catastro;
Problem is I�??m not able to confirm the results because querying the versioned view takes a lot of time and after several minutes I receive the error message�?�
�??Error starting at line 3 in command: SELECT count(*) FROM GISADMIN.parcelario08_evw p,GISADMIN.municipios m WHERE sde.st_intersects (p.shape,m.shape) = 1 AND m.municipios='VILLALBA' order by p.num_catastro Error report: SQL Error: ORA-20011: Error generating shape. (SHAPE1) ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 100 ORA-06512: at "SDE.ST_RELATION_OPERATORS", line 340�?�
Another test was ran using another layer (non versioned) instead of the parcels fc and the performance is very bad (at the time this document is being edited, the oracle session is going for 30 minutes indicating the wait event �??External Procedure call�?�). I like using sql functionality to generate reports quickly and used to copy data to Postgis (no ESRI software) just to have this ability. In postgis the latter statement took just 2 seconds. The gdb is compress and maintain daily. After about 2 hours, I received the following errors�?�
�??ORA-29903: error in executing ODCIIndexFetch() routine ORA-28576: lost RPC connection to external procedure agent ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 933 ORA-06512: at "SDE.SPX_UTIL", line 3954 ORA-06512: at "SDE.SPX_UTIL", line 3832 ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 882 29903. 00000 - "error in executing ODCIIndexFetch() routine" *Cause: The execution of ODCIIndexFetch routine caused an error. *Action: Examine the error messages produced by the indextype code and take appropriate action.�?�