471,073 Members | 1,465 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,073 software developers and data experts.

Number of elements in "IN operator"

Hi ,

Does anybody know -how many elements could be in SQL operator IN?.

As an example DELETE FROM emp WHERE ename IN (NULL, 'king',...,N);

Thank you in advance .
Jul 19 '05 #1
3 19065
"Mikhail" <mi*************@hotmail.com> wrote in message
news:51**************************@posting.google.c om...
Hi ,

Does anybody know -how many elements could be in SQL operator IN?.

As an example DELETE FROM emp WHERE ename IN (NULL, 'king',...,N);

Thank you in advance .


I beleive PL/SQL functions/operators are limited to 64K parameters in 9i
Jul 19 '05 #2
Mikhail wrote:
Does anybody know -how many elements could be in SQL operator IN?.

As an example DELETE FROM emp WHERE ename IN (NULL, 'king',...,N);

Thank you in advance .


I've never run into a limit, but then again I don't make a habit of
using long lists of literals. If you use a sub-query i.e.

DELETE FROM emp WHERE ename IN (SELECT ename from bad_employee);

it's pretty much unlimited (i.e. how much processing power do you have,
and how long are you willing to wait.) I've run queries where the
sub-query that specifies values for the IN clause returns over half a
million rows.

--
//-Walt
// Seerch-a zee veb:
// <http://www.google.com/advanced_search?hl=xx-bork>
Jul 19 '05 #3

"Mikhail" <mi*************@hotmail.com> wrote in message
news:51**************************@posting.google.c om...
Hi ,

Does anybody know -how many elements could be in SQL operator IN?.

As an example DELETE FROM emp WHERE ename IN (NULL, 'king',...,N);

Thank you in advance .


If you are are meaning within SQL [and not PL/SQL] then as of Oracle 8.0 the
limit is 1000 literal items in an IN list.

In Oracle 7 this used to be something like 250 or 255. I do not believe it
has changed in Oracle 9.

A work of caution. The Oracle optimizer will use the values in this list so
it may pay to study the execution plan.

I hope this helps.
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

43 posts views Thread by markryde | last post: by
9 posts views Thread by Emanuele Aina | last post: by
6 posts views Thread by Gaijinco | last post: by
2 posts views Thread by babakandme | last post: by
3 posts views Thread by Mikhail | last post: by
8 posts views Thread by Ratko | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.