Thanks for the question, ammu.

Asked: July 02, 2016 - 11:08 am UTC

Last updated: August 13, 2019 - 2:10 am UTC

Version: 11.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

How many maximum number of logical operators(either AND/OR) are allowed in where clause in select statement.

eg

select *
from employees
where first_name = 'abc'
or
first_name = 'cde'
or
first_name = 'def'
or
....
....
.....
first_name = 'xyz';

Instead if i use in operator , the maximum number of list values it accept.

select *
from employees
where first_name in ('abc','cde','def','efg'........);

Thank you

and Chris said...

I'm not aware of a documented limit. Practically speaking you're more likely to hit issues with size of your SQL, parse times, memory available, etc. before having "too many" conditions.

What these limits are depends on your environment:

"The limit on how long a SQL statement can be depends on many factors, including database configuration, disk space, and memory"

http://docs.oracle.com/database/121/REFRN/GUID-685230CF-63F5-4C5A-B8B0-037C566BDA76.htm#REFRN0043

For example, the following generates 249,999 or clauses:

SQL> declare
  2    cur pls_integer;
  3    sql_stmt dbms_sql.VARCHAR2a;
  4    rws pls_integer;
  5    buf varchar2(100);
  6    upper pls_integer := 250000;
  7  begin
  8
  9    sql_stmt(1) := 'select * from dual where 1=1';
 10    for i in 2 .. upper loop
 11      sql_stmt(i) := ' or 1=' || i;
 12    end loop;
 13
 14    cur := dbms_sql.open_cursor;
 15    dbms_sql.parse(cur, sql_stmt, 1, upper, false, dbms_sql.native);
 16    rws := dbms_sql.execute(cur);
 17    dbms_sql.close_cursor(cur);
 18  end;
 19  /

PL/SQL procedure successfully completed.

If you're writing SQL that gets anywhere this many and/or clauses you should rethink your query and/or schema design!

You can have up to 1,000 expressions in an in list

http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions013.htm#SQLRF52169

You can compare more than 1,000 things in an in condition though. To do this, use a subquery:

where col in (select col2 from subquery_returning_gt_1000_rows)

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

maximum count of value in IN CLAUSE

Tuple

More to Explore

PL/SQL demos

PL/SQL docs

the maximum number of logical operators(AND/OR) can be used in where clause

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

maximum count of value in IN CLAUSE

Tuple

More to Explore

PL/SQL demos

PL/SQL docs