Index recommendations of DefQuery attributes?

[MartinAmeskamp]

Hi, we've been using MxdPerfStat for years to analyze slow MXDs for years (mostlym on Oracle databases), and it's a great tool. There's one thing that I don't quite understand, though: Whenever MxdPerfStat finds a layer with a definition query that refers an attribute that doesn't have an index, there's a warning ("check if index exists on attribute..."). The same warning comes up when analyzing an MXD for publishing to ArcGIS Server.

Why is that? In my experience, attributes used in definition queries tend to be non-selective, with a handful of different values (status, pressure level, type of building, annotation classid...) shared between many features. The database almost never uses these attributes to access the data for display in ArcMap since the spatial query is invariably more selective (unless the feature class is very small, and then indexes don't matter anyway). I've even seen plenty of cases where the presence of an attribute index makes Oracle use that index, resulting in much slower query execution.

Are our databases special in the we don't need these indexes and everybody else is happy with them??

Thanks a lot, Martin

[JoshuaBixby]

Any attribute listed in the definition query is part of an SQL WHERE statement.  Generally, and there are always exceptions, searches on data are sped up by the presence of indexes.  Since MXDPerfStat and the publishing tools don't know the actual contents of the data set, and they can't predict the execution plan of the database engine, they have implemented general rules of thumb for improving performance.  If you know the data sets well, and know that adding an index may actually slow down an SQL WHERE statement, then remove then index and ignore the warning, which is seems like you are already doing.