I guess I should have read the documentation here:
http://webhelp.esri.com/arcgisserver/9.3/java/index.htm#geodatabases/creating-95168347.htm
The solution was an Oracle 'create index' query a la:
create index sa_idx on st_parks(shape)
indextype is sde.st_spatial_index
parameters('st_grids=1000 st_srid=5');
Ran into a 'maximum number of grids per feature' exceeded error, and then realized that the grid size is a function of the measurement unit of the spatial reference. So by originally passing in st_grids=1 (like the documentation) it was creating a 1 foot spatial index grid...and yeeeah...that was a little excessive.
Hopefully Esri will fix their create spatial index tool to accommodate Oracle.
Well, no one is gonna answering. Therefor based on my own tests, I guess I can provide some useful information in case anyone interested in sde attachments.
Storage of any attachment in file server (IIS in my case) provides unbeatable export speeds, especially in case of facing concurrent requests. However, the performance issue in using URL method here is that the users need to fetch the URLs before starting to download the files. I realized that the actual bottleneck of the system is not the storage tier (DB or File-Server) but it is the ArcGIS server itself, which should provide the users with some attribute data, i.e the attachment URLs or attachment information.
In brief, my performance results show that:
(1) In both methods, arcgis server is the main source of delays that users experience. For example SDE's default configuration could export each file in 0.140-0.218 seconds on my system and IIS in 0.0198 sec. However, the total process of acquiring URLs from GIS server and downloading the files takes about 0.839 sec. This value is very close to 0.814 sec for SDE method which consists of fetching the "attachment info" through the REST interface and downloading the files.
(2) When focusing on batch exports, for example migration or backup scenarios, we can expect less drawback on behalf of DB for larger datasets. I realized that performing queries on large tables can provide faster export speeds in comparison with url requests to IIS. It means that DBs are more able to reduce overheads "per feature" when facing batch export scenarios. Nevertheless, I should emphasis here that we used the term "batch export", which by that we mean the BLOB data is not turned into real files in this process. Conversion of BLOBs to files, which is necessary for the "end-users", definitely requires client-side or server-side process and perhaps will end in results similar to (1).
(3) A research article about the break-even point of using BLOBs and FS was interesting for me, since it used a fully controlled environment in testing the performance of DBs and files system storages.To blob or not to blob
At last my most important point, which is not performance-related, is:
SDE attachment method provides very good consistency. In case any errors happen, SDE just rolls back. However, when relying on file servers you need to develop an especial process yourself to maintain the consistency of your attachments. You need to make sure that URLs are pointing to a valid address. Whenever an incomplete attachment upload, a change in feature attachment or a feature/attachment delete occurs you need a process to make sure that everything is fine. SDE on the other hand, takes advantage of transactions and even provides you with versioning.
So I believe that if you have your features in SDE, having your attachments in SDE too is "easier" and "not slower" than having them in File-Server.
Best Answer
The two most likely causes (that I can think of) are;
1) The SDE Geodatabase configuration keyword includes a PRECISION statement and you likely have converted the feature class form high precision to single precision. -- In most storage projections (we use Stateplane, for example) there is insufficient precision to get fine detail so vertices coalesce and shapes morph. NOTE that configuration keywords are used when using "copy/paste" to move geodatabases into- and out-of SDE.
2) The ArcCatalog environment settings >> Coverage section includes a Precision option for the default when creating new featureclasses. Similar to the keyword option above, the precision should most often be set to DOUBLE so that the coordinates for new featureclasses have sufficient numeric precision to handle fine geometric details.
So, check the environment settings on your client software (ArcCatalog > Geoprocessing > Environments... > Coverage > Precision) and work with your SDE administrator to review the KEYWORD settings.
As a quick test to determine if SDE is or is not the problem, try to copy/paste this featureclass from your source File GDb to a new Personal GDb. This process will make use of the Environment setting for new featureclasses and has nothing to do with SDE. If you get the problem results, then you know the problem is NOT SDE, but is your client software.