Do ESRI clients honor column indexes created with DBMS tools?

[ShaneBuscher]

Let's say you have some feature classes and tables that are registered with a GDB but are not not registered as versioned. You create spatial and column indexes on them within the DBMS (Not ArcCatalog). Will ESRI clients like ArcMap, ArcGIS Pro, ArcGIS Server, etc recognize and honor those indexes? In other words, is ArcCatalog required to create indexes for ESRI clients? If you don't use ArcCatalog will performance suffer? 

[VinceAngelo · Accepted solution]

It's not ArcGIS which would need to honor the indexes, but the database itself (since ArcGIS just issues SQL queries, and lets the database do its thing).

If you use ArcGIS tools to create database indexes, they are database indexes, not Esri indexes, so all clients have the potential to have those indexes used.  The database optimizer makes the final call as to when to use an index or not. If hints haven't been applied, both Esri and non-Esri clients should have the same performance characteristics.

- V

[ShaneBuscher]

Thanks Vince for the explanation that all clients will honor indexes created with either ArcGIS or DBMS tools. ESRI documentation should make this clear as it indicates to exclusively use ArcGIS. 

[VinceAngelo]

There are certainly some cases where you do want to only use ArcGIS tools for index construction, mostly involving versioning or archiving.  In those cases database tools are unable to discern what other tables need the same indexes, and performance could be harmed as a result.  Using database tools takes a large burden on your own shoulders to do the right thing, so the documentation isn't going to recommend you place yourself in that situation.

- V

[BillFox]

Does the same go for unique constraints at the DBMS level with versioned and non-versioned feature classes?

[VinceAngelo]

It's not possible (or wise) to enforce unique constrains on versioned feature classes, since edits can result in duplicate attributes in different versions (causing conflicts in the ADDs table). As a rule, you should choose whether you want the geodatabase model to control a table or not, and register only those tables over which you are willing to relinquish control. Piecemeal management of registered tables causes most of the anguish tossed into the laps of my colleagues in Tech Support. I recommend you don't make their new year a difficult one.

- V