| Maximum constant values in IN operator in SQL query [message #215325] |
Sun, 21 January 2007 06:52  |
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 #215344 is a reply to message #215337] |
Sun, 21 January 2007 12:33  |
Frank
Messages: 7901
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 #215437 is a reply to message #215387] |
Mon, 22 January 2007 05:16 |
 |
Mahesh Rajendran
Messages: 10707
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 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
