Why oracle IN clause has limit of 1000 only for st

Question

Oracle IN clause has limit of 1000 for static data,but it accepts unlimited data from sub queries. why?

Answer 1

Try using 'exists' than 'in'.You can as well create sub queries using 'exists'.

Answer 2

This is because IN has very poor performance with large number of values in the list. It's just shortcut for OR clause, and at the database level the engine will change IN to OR's.

You should also avoid doing subqueries inside IN clause - better use EXISTS.

Answer 3

It's a restriction on any expression list:

A comma-delimited list of expressions can contain no more than 1000 expressions.

Why 1000? Presumably the implementation needs some kind of limit, and that probably seemed like more than enough. There may well be, or certainly may have been when that limit was set decades ago, a performance reason for the limit as well, particularly as the IN is converted to multiple OR statements by the optimiser in this case (which you can see if you look at the execution plan).

I'd struggle to come up with a reasonable scenario that needed to get anywhere near that, with fixed values that couldn't be derived from other data anyway as a subquery.

I suspect it's somewhat related to the logical database limits which say you can't have more than 1000 columns in a table, for instance; since an expression list is used in an insert statement to list both the columns and the values being inserted, the expression list has to be able to match that, but maybe has no reason to exceed it.

Speculation of course... without seeing the internals of the software you're unlikely to get a definitive answer.