In Oracle we have the limitation of only 1000 items supported in the IN clause of SQL. I would like to know if there are any other such limitations in Oracle.
Asked
Active
Viewed 676 times
2
-
21000 items in an IN is a lot.... you may want to reconsider your SQL if you are approaching this limit. – port5432 Oct 21 '13 at 12:20
-
1It might be a lot, but I have to contend with it quite a lot. It depends on the nature of the task at hand. – Dan Bracuk Oct 21 '13 at 12:35
-
A simple alternative is to use a bulk insert into a temporary table and use an `IN` on a subquery on -- or just a join to -- said temporary table (in case anyone is interested in ways around the in-1000-item-limitation). – Colin 't Hart Oct 21 '13 at 15:11
-
Too broad - unless you're satisfied that the answer to "is there are any other such limitations in Oracle" is, "yes". – Jeffrey Kemp Oct 22 '13 at 05:34
-
possible duplicate of [Limitation on IN()](http://stackoverflow.com/questions/19304570/limitation-on-in) – Ben Jan 03 '14 at 10:28
3 Answers
3
Various limits are documented here for 11.2: http://docs.oracle.com/cd/B28359_01/server.111/b28320/limits003.htm
Some edition-based limitations: http://www.oracle.com/us/products/database/enterprise-edition/comparisons/index.html
David Aldridge
- 51,479
- 8
- 68
- 96
2
Limits for Oracle 10g:
http://docs.oracle.com/cd/B19306_01/server.102/b14237/limits.htm
Logical limits: http://docs.oracle.com/cd/B19306_01/server.102/b14237/limits003.htm
Physical limits: http://docs.oracle.com/cd/B19306_01/server.102/b14237/limits002.htm
Colin 't Hart
- 7,372
- 3
- 28
- 51
1
There is a simple trick to bypass this limit.
I use it sometimes in ad hoc queries.
Tested on Oracle 11.2g for 5000 items.
Drawback of this method is long parse time (about 5-10 seconds on my system for 5000 items).
WITH list AS (
select 1 as X from dual union all
select 2 from dual union all
select 3 from dual union all
......
......
......
select 4997 from dual union all
select 4998 from dual union all
select 4999 from dual union all
select 5000 from dual
)
SELECT /*+gather_plan_statistics */ * FROM table123
WHERE x IN ( SELECT * FROM list );
select * from table( dbms_xplan.display_cursor (format=>'ALLSTATS LAST'));
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5000 |00:00:00.24 | 29 | | | |
|* 1 | HASH JOIN RIGHT SEMI| | 1 | 1 | 5000 |00:00:00.24 | 29 | 1066K| 1066K| 1339K (0)|
| 2 | VIEW | VW_NSO_1 | 1 | 5000 | 5000 |00:00:00.24 | 0 | | | |
| 3 | VIEW | | 1 | 5000 | 5000 |00:00:00.21 | 0 | | | |
| 4 | UNION-ALL | | 1 | | 5000 |00:00:00.18 | 0 | | | |
| 5 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
| 6 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
| 7 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
| 8 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
| 9 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
..........
..........
..........
|5000 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
|5001 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
|5002 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
|5003 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
|5004 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
|5005 | TABLE ACCESS FULL | TABLE123 | 1 | 9999 | 9999 |00:00:00.02 | 29 | | | |
----------------------------------------------------------------------------------------------------------------------
krokodilko
- 35,300
- 7
- 55
- 79
-
That's a good trick. You can significantly improve performance by combining many of the `SELECT`s together like this: `select column_value x from table(sys.odcinumberlist(1,2,3,... )) union all ...`. Although building a string like that is more difficult. – Jon Heller Oct 22 '13 at 04:26