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?

JoshuaBixby

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.