8

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

Vivek Baranwal
  • 187
  • 1
  • 2
  • 10
  • Do not question Oracle Mightiness, it is forbidden for common user :D Just kidding. You can override it with WHERE par_id IN ('a','b', ...,'z') OR par_id IN ('or','pr'...'zd') – DARK_A Sep 25 '13 at 10:52
  • Numerous ways to override this (I found 3 or 4 in stackexhange).. But the reason might simply be like basic constraints in Oracle pre load, max bytes in query etc – Srini V Sep 25 '13 at 10:57
  • If you've got more than a dozen or so entries, you should be using a subquery of some sort anyway. 1000 is an arbitrarily large limit - probably already too great. – Jeffrey Kemp Sep 26 '13 at 01:40

3 Answers3

11

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.

dregad
  • 1,150
  • 8
  • 21
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • 1
    Interesting co-relation between the number of columns and the number of elements in an expression list. Never thought that this might be related. –  Sep 25 '13 at 11:18
1

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.

schabluk
  • 708
  • 8
  • 17
  • 1
    `IN` and `EXISTS` are logically equivalent. They are processed the same way by the Oracle optimizer. You should use either of them at your discretion, it won't matter from a performance point of view. – Vincent Malgrat Sep 25 '13 at 11:11
0

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

Mickee
  • 19
  • 3