Do ESRI clients honor column indexes created with DBMS tools?

767
5
Jump to solution
12-27-2017 08:49 AM
ShaneBuscher
Occasional Contributor

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? 

1 Solution

Accepted Solutions
VinceAngelo
Esri Esteemed Contributor

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

View solution in original post

5 Replies
VinceAngelo
Esri Esteemed Contributor

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
Occasional Contributor

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. 

0 Kudos
VinceAngelo
Esri Esteemed Contributor

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
MVP Frequent Contributor

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

0 Kudos
VinceAngelo
Esri Esteemed Contributor

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