EXTPROC/Oracle-index problem

2665
9
10-29-2013 01:57 AM
MortenHansen
New Contributor II
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
9 Replies
VinceAngelo
Esri Esteemed Contributor
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
0 Kudos
MortenBødtkjer
New Contributor II
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)
0 Kudos
VinceAngelo
Esri Esteemed Contributor
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).
0 Kudos
ForrestJones
Esri Contributor
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
0 Kudos
MortenBødtkjer
New Contributor II
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
Esri Esteemed Contributor
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
0 Kudos
MortenBødtkjer
New Contributor II
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
0 Kudos
MortenBødtkjer
New Contributor II
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
0 Kudos
VinceAngelo
Esri Esteemed Contributor
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