EXTPROC/Oracle-index problem

[MortenHansen]

Hello,

I have installed the EXTPROC (only the st_geometry.dll part) on an Oracle 10.1.0.1 (and 10.1.0.3) and use SQL directly to my ArcSDE geodatabase instance (ArcGIS 10.1).

It works fine, except that I get an error when my SQL query uses a spatial index (like in the example below).

The error message (including SQL statement) is:

Error starting at line 1 in command:

SELECT COUNT(objectid) FROM myfeatureclass WHERE sde.st_intersects(shape, sde.st_geomfromtext('POINT(-50.7 64.6)', 4326)) = 1
Error report:
SQL Error: ORA-29902: error in executing ODCIIndexStart() routine
ORA-28579: network error during callback from external procedure agent
ORA-06512: ved "SDE.ST_GEOMETRY_SHAPELIB_PKG", linje 870
ORA-06512: ved "SDE.SPX_UTIL", linje 2927
ORA-06512: ved "SDE.SPX_UTIL", linje 3206
ORA-06512: ved "SDE.ST_DOMAIN_METHODS", linje 299
29902. 00000 -  "error in executing ODCIIndexStart() routine"
*Cause:    The execution of ODCIIndexStart routine caused an error.
*Action:   Examine the error messages produced by the indextype code and
           take appropriate action.

I really hope someone can help me solve the problem?

Thanks,
Morten

[VinceAngelo]

Oracle 10gR1 hasn't been supported in a long time (10.1.0.1 was never supported at all).
It certainly isn't supported for use with ArcGIS 10.1.  I don't think you can ever get this
configuration to work.

- V

[MortenBødtkjer]

Sorry, It was a typing mistake. I meant Oracle version 11.2.0.1.

I have also tested it on 11.2.0.3, since I suspected some bug fixes in the Oracle patch to be related to my problem. However it didn't make any difference.

Thanks,
Morten

(boedtkjer=mbhansen)

[VinceAngelo]

A search on the 28579 error turned up an Oracle bug.

I think it more accurate to say that "a query on a layer without an index" causes
this problem.  I always create my indexes as part of table creation, so I've never
seen anything like this.

- V

EDIT: RE-reading the Oracle defect description, it seems the ODCIindexstart is failing
even when the index exists, so my comment isn't applicable (I wish the editor had
a strikethrough option).

[ForrestJones]

Hi Morten,

It probably is something we need to fix or have already fixed...we'd have to look at the data ("myfeatureclass"). Would you be able to open a tech support incident and send the data, or even narrow it down to a particular geometry in the feature class that causes the failure, please?

Thanks

[MortenBødtkjer]

Thanks Vince and FJ

Yes, I could open a tech support incident.

It seems I can reproduce the error by creating a simple polygon feature class using the python script below, and then afterwards run the SQL query.

PYTHON:

import arcpy
import sys

sdeconn = "c:\\arcgisconfig\\oracle.sde"
prj_file_path = "c:/Program Files (x86)/ArcGIS/Desktop10.1/Reference Systems/World Time Zones.prj"
arcpy.CreateFeatureclass_management(sdeconn, "MYFEATURECLASS", "POLYGON", spatial_reference=prj_file_path, spatial_grid_1=0, spatial_grid_2=0, spatial_grid_3=0)

SQL:

SELECT COUNT(objectid) FROM MYFEATURECLASS WHERE sde.st_intersects(shape, sde.st_geomfromtext('POINT(-50.7 64.6)', 4326)) = 1

It makes no difference whether I include or exclude the "spatial_grid_1=0, spatial_grid_2=0, spatial_grid_3=0" parameters.

- Morten

[VinceAngelo]

If you don't specify an index grid size, ArcGIS will leave the table un-indexed, assuming you'll
get around to loading eventually.  Once data is added, then the index is built, using grid sizes
chosen from the characteristics of the first loaded dataset.

Questions:

• Is there data in your table?


• Is there a spatial index on that table?


• How is the index described (grid sizes)?

- V

[MortenBødtkjer]

Hi Vince,

Thanks for your reply.

• I have tried both with and without data => same problem


• As I understand a spatial index is automatically added. I do not add a spatial index explicitly. (also see console output below)


• My parameters are spatial_grid_1=0, spatial_grid_2=0, and spatial_grid_3=0, so I leave it to ArcGIS to determine the values.

If I run the python code below:

deconn = "c:\\arcgisconfig\\oracle.sde"
prj_file_path = "c:/Program Files (x86)/ArcGIS/Desktop10.1/Reference Systems/World Time Zones.prj"
fc = arcpy.CreateFeatureclass_management(sdeconn, "MYFEATURECLASS", "POLYGON", spatial_reference=prj_file_path, spatial_grid_1=0, spatial_grid_2=0, spatial_grid_3=0)

array = arcpy.Array([arcpy.Point(45, 50), arcpy.Point(47, 50), arcpy.Point(47, 49), arcpy.Point(45, 50)])
polygon = arcpy.Polygon(array)
cursor = arcpy.da.InsertCursor(fc, ("SHAPE@"))
cursor.insertRow((polygon,))
del cursor

desc = arcpy.Describe(fc)
print "Feature Type:  " + desc.featureType
print "Shape Type :   " + desc.shapeType
print "Spatial Index: " + str(desc.hasSpatialIndex)

The output from my console is:

Feature Type:  Simple
Shape Type :   Polygon
Spatial Index: True

And I still get the error from my SQL Query.

- Morten

[MortenBødtkjer]

Hi again,

I have just figured out that if I use the parameter spatial_grid_1=1 instead of spatial_grid_1=0 in my python script, my SQL Query works.

So it seems that I need to set the grid values explicitly.

Thanks a lot for helping me so far!

Now I hope this will help me solving my original problem, which is a bit more complicated.

- Morten

[VinceAngelo]

This is a bit of a slippery slope, because it's not enough to set a spatial index size --
You need to set an appropriate spatial index size.  This is why ArcGIS started
delaying the calculation process until a better idea of the data characteristics
was available. 

It is, however, generally safe to establish a trivial index (e.g., 100 degrees for a
global dataset in GCS_WGS_1984 coordinate system), then after data load
is complete, rebuild to something which will perform better.

- V