pi_inval has comma seperated values in it. The problem is that in an IN operator we can have maximum of 1000 constant values. How can we modify the query to accomdate more than 1000 constant values in an IN operator.

Just out of curiosity: this question pops up every now and then, and I just cannot think of a case where one would get a list of 1000+ items as input for such a query, unless these 1000+ items already came from a db-table.
Could you, just to satisfy my curiosity, tell us please what the origin of the list of 1000+ items is?

Frank, it is quite common.
In my case, the inlist contains around 15,000+ entries.
Source is a flat file and sometimes a database table. Has something to do with Bioinformatiocs/MicroArrays/Gene Sequencing. Process just runs fine along with a Perl parser.

Frank,
As Mahesh also suggests, I am getting this 1000+ comma seperated values from our .Net application which is contatenated. Well I don't want to go with the idea of putting the values into an GTT table as everytime the query is executed, I shall be inserting the whole list into a table and the procedure is being executed numerous times.

Well, in my case getting 1000+ values is not likely to be the case, but just wanted to know if there is a simple way to avoid the error, just in case we get 1000+ values in a single string.

@Frank
>>For the database tables: why can't you just join? Is this OLTP or DW?
Sorry. I did not make it clear. For database table it will be a direct join. For all non-database data, we insert into GTT and do a join.

My only problem with external table is, the directory (for the file) should be in server and this is a multiuser system.
Multiple users may 'upload' (sqlldr) or just 'compare' (IN list inserted into GTT for pattern search) their data with our database using a web interface.

@himang
I did not prefer a regular table because, if this is mult-user system ( seems yours is not), then a single GTT will maintain different sets of data and is visible only that particular session. Once the session quits, the rows are automatically purged.

Well I don't want to go with the idea of putting the values into an GTT table as everytime the query is executed, I shall be inserting the whole list into a table and the procedure is being executed numerous times.

So instead of the microscopically short amount of time it takes to insert the rows into a GTT, you are prepared to condemn Oracle to 1000+ index range scans?

The very good reason Oracle only allows short IN-lists is because they tend to be inefficient in large numbers. Oracle has only 2 ways to process them:
- As an driving index scan. For each value, Oracle performs a separate index scan and concatenates the results, which are then passed on to the next step in the SQL. This is equivalent to running 1000 SELECT statements.
- As a filter. For each row returned by the other query criteria, Oracle goes through the list one-by-one to filter non-matching rows. Not so bad over a few rows, but wait til you query 100,000+ and it has to perform up to 100,000,000 comparison operations.

Using a GTT, Oracle can still use (equivalents of) both of these methods, but has a third option - table join. This is MUCH more scalable for medium and large volumes. A HASH join can resolve the query with a single pass of the GTT, without the 1000+ index scans on the outer query.

Even for the smaller queries where the user only selects a few values, the overheads of inserting into a GTT are negligible, as is the cost of the IN-subquery over and above the IN-list.Ross Leishman