AnsweredAssumed Answered

Index recommendations of DefQuery attributes?

Question asked by ameskamp on Aug 12, 2016
Latest reply on Aug 12, 2016 by bixb0012

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

Outcomes