Default Sorting for SDE Feature Class

[MichaelVolz]

To All ArcSDE Users:

I have a feature class in SDE that I open up and see that the records are not sorted by the ObjectID field in ArcCatalog.  I thought this was the default field that ArcCatalog uses to sort SDE records when a feature class table is first opened in ArcCatalog.

What would cause an SDE feature class table not to be sorted by ObjectID by default?

Additional information: This feature class has records appended to it from a join between another SDE feature class and a table in SQL Server 2005 that have common fields that are used for the join.  These records are appended on a daily basis.

[VinceAngelo]

As a rule, ArcSDE clients like ArcGIS do not specify an ORDER BY clause, which is
the only way to enforce a sort order on any column.  ORDER BY is an expensive
option which forces extra intermediate passes through the result set.

The registered rowid column (usually OBJECTID) is only required to be NOT NULL and
unique.  The population rules sometimes cause gaps or counterintuitive sequencing in
a simple "SELECT * FROM table" query, but spatial constraints will generate the
appearance of that as well.

- V

[LalitArora]

Hi Vince

I found this post mostly matching to my issue. I'm using ArcGIS Version 10.2.2.

In my GP script, I deleted all old features from a FC using arcpy.DeleteFeatures_management.

and Inserted new features by using arcpy.da.insertcursor, record inserted in the correct order, I can see from auto generated object ids.

But on opening attribute table in ArcMAP or preview in ArcCatelog the table don't show records sorted on Object ID. I tried to rebuild indexes, set spatial index on FC using owner. It did not help

If I use CopyFeatures, FeatureClassToFeatureClass, Append management tools, features show up correctly sorted on ObjectID. In my GP scenario I'm not the owner of dataset/feature class. I'm working with another user having privilege to insert update delete records.

What's the difference. How I can see data on sorted order?

Thanks and Regards

Lalit Arora

[VinceAngelo]

Once you start using DELETE, the database is well within rights to place rows in any page it deems appropriate (each table has its own "high water mark" of previously allocated extents).

If you actually TRUNCATE the table instead of DELETE, then the rows would likely be placed in the order you inserted them (since the high water mark is cleared, and new blocks are being allocated)

It's important to know that the database is not obligated to return rows in any particular order unless an ORDER BY clause is used.  Also, once the spatial extent reaches a quarter of the overall extent, spatial queries are likely to return features in spatial index order.

- V

[LalitArora]

Thanks Vince for prompt reply.

I'm not deleting table. GP Script user is not the owner of the table. So it can not create or delete schema. I'm just deleting existing features and inserting new one in exiting schema.

DeleteFeatures_management - deleting rows

arcpy.da.insertcursor - inserting rows

If I use CopyFeatures, FeatureClassToFeatureClass, Append management tools, output features show up correctly sorted on ObjectID. I can't use these tools in present case.(limitation in my scenario)

If same feature data set (i.e. same spatial extent) show up differently created by above two ways.

What's missing to show it up correctly?

[VinceAngelo]

In database terms, DELETE is something that happens to rows. Table deletion is referred to with the directive DROP.  If you did DROP the table it wouldn't be an issue, since TRUNCATE is a lot like dropping the table then recreating it, without the mess of having to repopulate the database catalog and recreate all the indexes.

I did state that rows inserted into blocks allocated to a table's free list would produce random ordering.  I didn't go into it the why, but there's an entire domain of Computer Science to choose how to most efficiently reallocate an existing list of storage, and the algorithms most likely to make most efficient use of the space are also least likely to result in features physically stored in insertion order.

You should use the Truncate Table tool I referred to in my answer, instead of Delete Features, but if you aren't replacing contents as the table owner, then you'll need to research the proper way to construct a stored procedure to permit that to happen (it's similar, but different in Oracle​ and SQL-Server​).  Depending on the geometry storage model, you may need to manually TRUNCATE ancillary tables as well.

- V

[LalitArora]

Thanks Vince

I got your explanation regarding physical storage of data on disk and completely agree to it.

Only limitation I'm not the owner of data set. So I also cant use Truncate tool.

If i was owner, I would have used FeatureClassToFeatureClass tool, which deleted old and create new one in one statement. I'll explore stored procedure option as you told.

One doubt still I have. rows inserted into a feature dataset with Append_management tool shows correctly sorted data based on ObjectId. Its just add more features in to existing dataset without creating it again. Internally It should also have created a cursor to push new data and same physical allocation algorithm applied to this as well. How differently it worked?

[VinceAngelo]

Best practice-wise, if you're replacing the contents of a table, you should be doing so as the owner.

Only the owner has the ability to:

• Truncate a table,
• Place it in load-only I/O mode,
• Quickly load the table, then
• Toggle normal I/O mode, rebuild the indexes, and update the statistics.

Even if you accomplish the TRUNCATE without being the owner, all the others are REQUIRED for adequate performance; you're setting yourself up for a 7-ball juggling contest while placing one arm in a sling and belting yourself to a unicycle.

II don't understand your description of the data flow, but if features are allocated in sequential order, it's likely because the free list was empty or exhausted by bulk insertion.

- V

[LalitArora]

Thanks Vince.