Versioned Geodatabase - Problems with Delta (adds/deletes) tables never truncating

Question

Hello,
I am working with an enterprise GIS systems that consists of a multiversioned geodatabase and ArcGIS server map services/web sites.  Here are some basics about our system:

ArcGIS Server 10.0/Oracle 11g
5 editable feature classes, each with their own version (using the "multi-project" version scenario).
Edit workflow consists of users connecting to their version of the database, bulk deleting records, and then reloading new records into the feature class.  
Total features in feature class are about 200,000, with about 5,000 edits per day.  
We have a number of spatial views using the feature classes.  We have looked into using multi-version views, but they don't seem to give us enough flexibility for the complexity of the views we are using.

So our problem...when users edit the feature classes, the reconcile/post up to default once complete.  In ArcMap, the default/production data looks fine.  But the spatial views do not reflect the updates, since they are not aware of the adds/deletes tables.  We have tried to reconcile/post everything, then compress the geodatabase, but that does not fix the problem.  Adds/deletes tables still have all the modified data, while the base table does not reflect the edits.  Only "solution" to this is to unregister/re-register the feature class.  This causes problems cause we can't continually stop the map services to remove the locks to perform this "maintenance" on the geodatabase.  We went to SDE for this data for the "enterprise" solution.  But the solution is a manual one, and requires down time of the map services/web site on a daily basis.

Also, with the number of edits to the tables, the general performance of the feature class comes to a crawl after a few days, due to the excessive number of records in the delta tables.  We have traced the SQL in Oracle...and there are some interesting joins going on between the lineages/states/adds/deletes/base tables...

Hopefully we are just missing some configuration/workflow issue and we can get this problem resolved 🙂

Any thoughts/suggestions are welcome!

Wendy

WendyTurner · Answer

Hi Cheryl,
Thanks for the suggestion.  Unfortunately, I don't think that is going to work either.  With the locks that ArcGIS Server puts on our feature classes, and the number of edits/reconciles/posts that go on in the database, it would be tough to manage that workflow.  We have thought about writing some Oracle packages to take care of pushing the edits up to the base feature class tables, but we weren't sure if that would bite us in the end.  We do have a support request in with Esri as well, and are also working with them on possible suggestions.  I will try your suggestion of performing the additional reconcile, then compressing...and see if that works.  
Thanks!
Wendy

CherylCleghorn · Answer

Wendy, the most ideal workflow is to delete all versions after the stated reconcile/post workflow, compress and recreate the versions. If this is not an option then, after reconciling posting all versions up the version structure to Default, you can reconcile all versions again with Default(without posting); then compress the geodatabase. Feel free to contact Support Services if you need additional assistance.

regards
Cheryl

ducksunlimited · Answer

if a gdb is not an option, you can create another sde gdb holding data through 1-way replication to serve web applications. 
Hi Jake,
Thanks so much for your suggestions. Unfortunately, we need to keep all the feature classes within Oracle in order to join them up with other Oracle tables that are within the database.  There is other non-spatial data that is ingested into the database that we need to join up to the feature classes.  These spatial views are what is used in the ArcGIS Server web apps.   It just seems like we are having to do a lot of workarounds in order to get this working properly 😞
Wendy