ammu ham, July 05, 2016 - 2:43 am UTC
Thank you Tom
maximum count of value in IN CLAUSE
arden, August 08, 2019 - 7:16 am UTC
I got a ORA-00913 "too many values" with a SQL query use IN CLAUSE.
the SQL looks like below:
select count(*)
from sample_table
where sample_table.key in ( 1,2, ... ,999)
OR sample_table.key in (1000,1001,...,1998)
...
OR sample_table.key in (99901,99902, ... 100899)
and I found there has a limitation about count of condition values in IN CLAUSE is 65535. below SQL running well without error:
select count(*)
from sample_table
where sample_table.key in ( 1,2, ... ,999)
OR sample_table.key in (1000,1001,...,1998)
...
OR sample_table.key in (64936, 64936, ... 65534)
I understand that I have to redesign my query but just notice there has a limitation.
August 09, 2019 - 6:32 am UTC
Thanks for that, I was unaware of that upper limit.
But basically, if you mess around at the boundaries, you're always going to discover issues
<code>
SQL> declare
2 c clob := 'select count(*) from t where x = 0';
3 v int;
4 begin
5 for i in 1 .. 50000
6 loop
7 c :=c || ' or x = '||to_char(i);
8 end loop;
9 execute immediate c into v;
10 end;
11 /
declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 9888
Session ID: 135 Serial number: 37042
<code>
Tuple
A reader, August 12, 2019 - 2:42 pm UTC
DECLARE
c CLOB := 'select count(*) from testdidixxx t where (1,x) IN (';
v INT;
BEGIN
dbms_output.put_line(systimestamp);
FOR i IN 1 .. 50000 LOOP
c := c || '(1,' || to_char(i) || '),';
END LOOP;
c := rtrim(c,',');
c := c || ')';
dbms_output.put_line(systimestamp);
EXECUTE IMMEDIATE c
INTO v;
dbms_output.put_line(systimestamp);
END;
August 13, 2019 - 2:10 am UTC
I'll repeat what I said
But basically, if you mess around at the boundaries, you're always going to discover issues
SQL> set serverout on
SQL> DECLARE
2 c CLOB := 'select count(*) from testdidixxx t where (1,x) IN (';
3 v INT;
4 BEGIN
5 dbms_output.put_line(systimestamp);
6 FOR i IN 1 .. 50000 LOOP
7 c := c || '(1,' || to_char(i) || '),';
8 END LOOP;
9 c := rtrim(c,',');
10 c := c || ')';
11 dbms_output.put_line(systimestamp);
12 EXECUTE IMMEDIATE c
13 INTO v;
14 dbms_output.put_line(systimestamp);
15 END;
16 /
ERROR:
ORA-03114: not connected to ORACLE
DECLARE
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 8760
Session ID: 5 Serial number: 30109
mathguy, August 12, 2021 - 3:08 pm UTC
Hi. I believe both the answer and the example (a query with 250,000 OR-delimited conditions) are wrong.
There seems to be a limit of 65,535 conditions in an OR-delimited compound condition. (Probably the same for AND.) If I were to speculate, such conditions are processed as a stack, stacks are implemented using arrays, and array indices are unsigned 16-bit integers; largest value 65,535. The question has nothing to do with the limit on IN lists - which are expanded as OR-delimited compound conditions. By the way, if this is correct, I saw no trace of it in the documentation.
As to the example: The optimizer, as the query is parsed, can determine if some conditions are tautologically TRUE or FALSE. It will not place a tautologically FALSE condition on the stack; and if at least one condition is TRUE, it will remove the compound condition from the WHERE clause altogether. All of this can be seen easily in an Explain Plan So, your example doesn't actually prove anything, since the very first condition is already tautologically TRUE.