How long can a definition query be?

Question

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?

AndresCastillo · Answer

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

JoshuaBixby · Answer

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.