topic How long can a definition query be? in ArcGIS Enterprise Questions

How long can a definition query be?

MatthewLeonard — Fri, 03 Jan 2020 19:24:58 GMT

I'm just wondering if there is any limit on the length of a definition query statement - by number of characters, maybe, or otherwise?

I'm using ArcGIS Desktop 10.3.

I just wrote a definition query statement like this...

OBJECTID IN ('VALUE1', 'VALUE2', 'VALUE3', etc...)

...but with many more values in the list. In my example I had 300 values in the list. So, for future reference, at what point would the list be too long for ArcMap to handle?

Re: How long can a definition query be?

JoshuaBixby — Fri, 03 Jan 2020 23:35:42 GMT

When working with definition queries, ArcGIS clients simply pass the conditions through to the data store tier, e.g., file geodatabase, SQL Server, Oracle, Postgres, etc.... As far as I know, Esri does not place a limit, per se, on the string containing a definition query. So, the answer really depends on what back-end data store you are working with in ArcGIS. Oracle is commonly believed to limit IN clauses to 1,000 items, and Microsoft uses the open-ended language "many thousands of values." I don't think Esri has formally stated the limit for file geodatabases, but it is quite large, thousands if not tens of thousands.

Beyond some limit that you may or may not hit in the future, large IN clauses are not very efficient in terms of execution plans. If you are working with thousands of values in an IN clause, there is likely a much better performing approach to selecting/filtering the records.

Re: How long can a definition query be?

AndresCastillo — Wed, 31 Mar 2021 02:14:00 GMT

Here are some other resources about this topic:

https://gis.stackexchange.com/questions/141099/definition-query-expression-limits-in-arcgis-10-2-2

https://community.oracle.com/tech/developers/discussion/235143/ora-01795-limit-on-in-clause

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:778625947169

https://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause