Why are definition query clauses limited to 1,000 values?

[Bud]

Generate Definition Query From Selection

A definition query clause has a limit of 1,000 values. If the selection includes more than 1,000 values, the resulting definition query will include multiple clauses.

I understand that 1,000 values in the IN clause is a lot (and unconventional in terms to SQL best practices). I understand why there might be a limit.

Buy why 1,000? Is it based on a known database limitation for certain kinds of DBs?

[Robert_LeClair]

From the Esri Support AI Chatbot - "The "Generate Definition Query From Selection" geoprocessing tool in ArcGIS Pro is limited to 1000 values to ensure performance and stability. Handling a larger number of values can significantly slow down the processing time and may lead to performance issues. If you need to work with more than 1000 values, consider breaking your selection into smaller subsets or using other methods to manage and query your data efficiently."  

Likely there are a few more reasons beyond this but I think it just comes down to performance and stability.

[JoshuaBixby · Accepted solution]

I think a big reason that 1,000 was picked specifically, instead of 500, 2000, or some other number, is that Oracle historically limited the number of expressions in a single IN condition to 1,000.  From IN Condition:

You can specify up to 1000 expressions in expression_list.

Oracle did raise that number to 65,535 with Release 23, but Esri still supports older versions.  So the 1,000 number is a lowest common denominator approach that will work across a range of database systems and not require a special one-off syntax just for Oracle.