Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Any size limit on IN-LIST in a SQL

Re: Any size limit on IN-LIST in a SQL

From: David Grzebien <dave_at_etci.net>
Date: Tue, 28 Nov 2000 23:58:16 GMT
Message-ID: <3A24484D.71DC331F@etci.net>

I believe the maximum number of values that you can place in the IN clause is 255 values.

From a DBA perspective, I would not recommend doing this for your query though, as you are throwing potentially distinct queries at the database that could flush out other queries from the shared pool and cause alot of hard parsing for every query that is submitted to the database like this. If this query is likely to be executed many times per hour, I would consider perhaps inserting the array into a table in the database, then access the data as a sub-query in the IN clause. How many concurrent users will be using the application?

Dave Grzebien

Madhu Konda wrote:

> Is there any limit on how long the parameters can grow in IN list of
> select statement. We have values in Java array, and want to use those
> values in sql statements' IN Clause.
>
> select col1
> from tab1
> where col1 IN ( value1, value2, ....size limit?);
>
> Thanks,
> MK
Received on Tue Nov 28 2000 - 17:58:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US