Home » SQL & PL/SQL » SQL & PL/SQL » Maximum constant values in IN operator in SQL query
Maximum constant values in IN operator in SQL query [message #215325] Sun, 21 January 2007 06:52 Go to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Hi,
I have a ref cursor, something like

 
declare
pi_inval varchar2(4000);

BEGIN
   OPEN ref_cur FOR
      'SELECT * FROM EMP WHERE EMP_ID IN (' || pi_inval || ')';

END;
/


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.

Regards
Himanshu
Re: Maximum constant values in IN operator in SQL query [message #215336 is a reply to message #215325] Sun, 21 January 2007 09:42 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
put the values in a table & SELECT FROM TABLE
Re: Maximum constant values in IN operator in SQL query [message #215337 is a reply to message #215325] Sun, 21 January 2007 09:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Instead of a regular table,
I would use a GTT and use a join.
Re: Maximum constant values in IN operator in SQL query [message #215344 is a reply to message #215337] Sun, 21 January 2007 12:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
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?
Re: Maximum constant values in IN operator in SQL query [message #215354 is a reply to message #215344] Sun, 21 January 2007 18:30 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Frank, it is quite common.
In my case, the inlist contains around 15,000+ entries. Smile
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.


[Updated on: Sun, 21 January 2007 18:31]

Report message to a moderator

Re: Maximum constant values in IN operator in SQL query [message #215376 is a reply to message #215325] Sun, 21 January 2007 22:20 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

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.

Regards
Himanshu

Re: Maximum constant values in IN operator in SQL query [message #215387 is a reply to message #215354] Mon, 22 January 2007 00:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Mahesh wrote:
Source is a flat file and sometimes a database table.

I still don't think I get it.
My first idea for the flat file would be external tables. For the database tables: why can't you just join?
Is this OLTP or DW?
Re: Maximum constant values in IN operator in SQL query [message #215398 is a reply to message #215387] Mon, 22 January 2007 02:17 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Frank
My first idea for the flat file would be external tables.

That's exactly what I thought.
Re: Maximum constant values in IN operator in SQL query [message #215402 is a reply to message #215325] Mon, 22 January 2007 02:46 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Seems to me like an issue described (and solved) on AskTom.
However if you are able to put the values into table, use it directly.
Re: Maximum constant values in IN operator in SQL query [message #215437 is a reply to message #215387] Mon, 22 January 2007 05:16 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
@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.

[Updated on: Mon, 22 January 2007 05:19]

Report message to a moderator

Re: Maximum constant values in IN operator in SQL query [message #215448 is a reply to message #215376] Mon, 22 January 2007 05:57 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
himang wrote on Mon, 22 January 2007 15:20
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
Previous Topic: How to recover deleted records? (merged)
Next Topic: How to suppress a line in a report
Goto Forum:
  


Current Time: Fri Dec 09 19:34:47 CST 2016

Total time taken to generate the page: 0.11031 seconds