How many parameters can sql query contain when being executed in Oracle 11g

Question

If I write a query like

select * from some_table where some_field in (1, 2, 3 ...)

then how long can this list be for query to be successfully executed?

Please note that if the list is longer than a dozen or so, there are probably better ways of doing it. — Jeffrey Kemp, Aug 30 '12 at 03:16

score 2 · Accepted Answer · answered Aug 29 '12 at 10:43

2

Quoted from

Oracle® Database SQL Language Reference
11g Release 2 (11.2)
Part Number E26088-01

"You can specify up to 1000 expressions in expression_list."

answered Aug 29 '12 at 10:43

Juha Laiho

596
2
8

Thanks, exactly what I wanted to know – Demarsch Aug 29 '12 at 12:23

score 1 · Answer 2 · edited May 23 '17 at 10:24

1

Does this question help?

Looks like it may be limited to 1000 items in the IN clause but you can get around that by sub querying. Be careful with a large number of items in the IN clause as it could affect performance.

edited May 23 '17 at 10:24

Community

1
1

answered Aug 29 '12 at 10:42

Phil Murray

6,396
9
45
95

Regarding Oracle 11 uses the same way for IN clause as well as EXISTS I'm pretty sure the performance will not be affected significantly. – Alexander Tokarev Aug 29 '12 at 11:58
Thanks, exactly what I wanted to know – Demarsch Aug 29 '12 at 12:23

How many parameters can sql query contain when being executed in Oracle 11g

2 Answers2