Skip to Main Content
  • Questions

  • Limit and conversion very long IN list : WHERE x IN ( ,,, ...)

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Michael.

Asked: September 15, 2000 - 1:06 pm UTC

Last updated: October 27, 2023 - 12:39 pm UTC

Version: Oracle 8.0.5

Viewed 100K+ times! This question is

You Asked

How many elements may be in the WHERE x IN (,,,) list ?
I see 2 ways to overcome IN list limitation:

1) use x=el_1 OR x=el_2 OR x=el_3 OR ...

2) create temporary table , but another question arise here:
why
create table A( X INTEGER, Y VARCHAR(32) );
takes up to 15 seconds sometimes ?

Regards, Michael.


and Tom said...



The same limit with IN is:

ERROR at line 42:
ORA-01795: maximum number of expressions in a list is 1000


DDL is very expensive and you should never dynamically create a table at runtime -- there is no reason to. You should just create the table ONCE and then:


insert into TEMP values ( ... );
select * from T where (a,b) in (select x,y from temp);
delete from TEMP;


Better yet, see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:146012348066 <code>
Please do not dyamically create a statement with hard wired IN list values or OR'ed expressions. That is the worst thing you can do to us. We'll spend more time parsing queries then actually executing them!!

Either use the static temp table or use one of the object tables or PLSQL tables as outlined in the link.


Rating

  (29 ratings)

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

Comments

ORA-01795

sanjai_9@yahoo.com, March 28, 2002 - 5:53 pm UTC

Tom,

We have a select statement where in we dynamically bind the t.e (please see the select statement below).The value of t.e depends on certain conditions. Using a FOR loop we dynamically bind the values in the IN clause.

But whenever the value exceeds 1000 we get this error. I read this page. Do we need to change our java code to handle this or is there a way to get around this.

SELECT t.a, t1.b , t.c, t.d
FROM t, t1
WHERE t.a = t1.a1
AND ( t.e IN (?, ?, ?, ?........ ) )

java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000

Thanks a lot.




Tom Kyte
March 28, 2002 - 8:57 pm UTC

Well, as the message sort of implies -- the max number of elements in an in-list (whether you BIND them or HARD code them -- please bind them) is 1,000.

You cannot exceed that.

Suggest you create a global temporary table, array insert your "in list" into this table and use


select .... and ( t.e in ( select * from global_temp_table );



Could you please tell me how can i handle ?

AdamWang, July 19, 2002 - 3:28 am UTC

Hi Tom,
I feel so trouble ,because my products of the CA must use "sleect * from a where b in(1,2,3,.......)".Then could you tell me another way to handle it.


Tom Kyte
July 19, 2002 - 7:28 am UTC

Look up cursor_sharing

search for it here, read about it in the docs, see chapter 10 in my Expert One on One.

Another solution for java

Michael, July 22, 2002 - 7:12 am UTC

This works quite good.

First:

CREATE TYPE NUMBERTABLE AS TABLE OF NUMBER;
/

Then in JAVA:

import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;

public class ArrayBindTest {

public static void main(String[] args) throws SQLException {

long longArray[] = new long[3000];
for(int i = 0; i < longArray.length; i++) {
longArray[i] = 4500 + i;
}
System.out.println("Starting ...");
DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@e2hp11:1521:destest", "scott", "tiger");
conn.setAutoCommit(false);

ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUMBERTABLE", conn);
ARRAY array_to_pass = new ARRAY(desc, conn, longArray);
OraclePreparedStatement pstat =
(OraclePreparedStatement) conn.prepareStatement(
"SELECT ENAME FROM EMP " +
"WHERE EMPNO IN (SELECT * FROM TABLE (SELECT CAST(? AS NUMBERTABLE) FROM DUAL))");
pstat.setARRAY(1, array_to_pass);
OracleResultSet rset = (OracleResultSet) pstat.executeQuery();
while(rset.next()) {
System.out.println(rset.getString(1));
}
pstat.close();
conn.close();
System.out.println("Finished ...");
}
}


A reader, August 28, 2003 - 3:38 pm UTC


Modify the parameter

oracleo, January 02, 2008 - 12:24 am UTC

Is there anyway that i can modiy the value of 1000 to a greater value at DB level...


Happy New Year!!
thanks
Tom Kyte
January 02, 2008 - 11:12 am UTC

no.

If you have 1,000 plus elements in your dynamic in list, you have made a mistake in design in my opinion (what end user would pick 1,000 discrete elements from a list box?!?!?)

Anyway, use a global temporary table

array insert your elements into it (using a bind variable of course)

and simply select * from t where c in ( select x from gtt );

very bind friendly
unlimited "in items"

In a database with only select privilege

Pradeep P, April 02, 2012 - 2:30 am UTC

Is there any way to do this in a production database where you have only "create session" and "read" access.?
Do we need to do the export/import of the table to another non- production database and continue with creating GTT
Tom Kyte
April 03, 2012 - 6:16 am UTC

why not ask for permission to be able to create a global temporary table in that schema?

I cannot imagine exporting/importing to run a simple query, that would be "not smart"

explain to the production database people that you can either

a) hit their database with a massive query where you list out each and every value - impacting their shared pool negatively and so on

or

b) you could have a global temporary table set up for them so they do not do that.


maybe they'll agree and do the right thing, you never know.

in VS or?

A reader, April 05, 2012 - 12:41 am UTC

Hi tom,

As we know, in () will eventually be handled as or by oracle. Is it done by optimizor for query-rewrite?

Then why not we directly write 'in' query as 'or' query to save oracle's time? Here you mentioned 1 issue
is that 'or' query might be very long and exhaust share pool easily, besides this, any other objections?
Tom Kyte
April 06, 2012 - 9:47 am UTC

it is done as a matter of parsing the query, yes.


it would save no time, it is such a trivial operation.

Instead of IN (), what about 1000+ select-literal-from-dual-union statements in a subquery?

Alan, April 06, 2012 - 2:06 pm UTC

I can imagine a functional user sending a developer an ad hoc list of 1000+ key values where those records need updated somehow. I have seen developers write multiple UPDATE statements in groups of 1000 key values to handle the ad hoc update.

I sidestep around the 1000+ limit by coding a CONCATENATE formula in the first row of a functional user's spreadsheet that builds a string like: "select 'cell_value' from dual union". Then: 1) cut-n-paste the formula into the remaining 1000+ spreadsheet rows; and 2) cut-n-paste all the CONCATENATE results into one big subquery.

Old way: IN (literal_1, literal_2, etc)
New way: IN (select literal_1 from dual union
select literal_2 from dual union
etc
)

How bad am I wearing out the parser? Is it really an issue if the SQL will only run once?

Tom Kyte
April 08, 2012 - 5:40 pm UTC

please use a global temporary table for this.

please do not union all 1000+ things - first - very very very bind "unfriendly"

second, - well, see #1


you'll run that sql once, but how many times do you do this "process"

just insert the data into a global temporary table and do "where in (select * from gtt)"

I use a lot of IN statement and get ORA-00913

Alexander, September 22, 2013 - 5:34 pm UTC

I have list with more than 1000 elements. So i write function which partitioned my List by 1000 elements like: (propertyName IN () OR propertyName IN (...) OR propertyName IN (...) ).

Dim goods As List = New List()
Dim rateValue as Integer = 100000
For i As Integer = 0 To rate
goods.Add(i)
Next


Dim Sql As System.Text.StringBuilder = New System.Text.StringBuilder()
Sql.AppendLine("SELECT G.ID, G.NAME")
Sql.AppendLine("FROM GOODS G")
Sql.AppendFormat("WHERE {0}", GetSqlListPartition(goods,"G.ID"))

When i execute this query the error is appeared: "ORA-00913: too many values".

But if i set rateValue = 10000 and generate query, it is executed ok. What is problem here?

how to insert IN List into table

Durga, May 22, 2018 - 10:50 am UTC

Hi Tom,

I am trying to do the following:
In my code the IN statement will be dynamically created and it may have more than 1050 values in it.
We want to handle these values without getting error -:
ora-01795 maximum number of expressions in a list is 1000

To insert these elements in table, i tried to use DBMS_UTILITY.COMMA_TO_TABLE function.
However, i faced issues as my values are all numbers and not varchar.

Can you suggest a way to insert these values into a GTT?

Also, to handle this error, this is what i am tried to do:
example:
select * from employees
where emp_id in (select * from table(sys.odcinumberlist(1,2,3,4 ….1020 values)));

However, i get error
ORA-00939: too many arguments for function

Can you please suggest how should i proceed?

Thank you.

Sincerely,
Durga
Connor McDonald
May 23, 2018 - 7:21 am UTC

You're on the right track with the nested table type (a GTT would work just as well).
To overcome the 1000 entries, just build it up in chunks, eg

SQL> create or replace
  2  package PKG is
  3
  4    procedure add(params sys.odcinumberlist);
  5    procedure reset;
  6    function vals return sys.odcinumberlist;
  7  end;
  8  /

Package created.

SQL>
SQL>
SQL> create or replace
  2  package body PKG is
  3    g sys.odcinumberlist;
  4
  5    procedure add(params sys.odcinumberlist) is
  6    begin
  7      for i in 1 .. params.count loop
  8        g.extend;
  9        g(g.count) := params(i);
 10      end loop;
 11    end;
 12
 13    procedure reset is
 14    begin
 15      g := sys.odcinumberlist();
 16    end;
 17
 18    function vals return sys.odcinumberlist is
 19    begin
 20      return g;
 21    end;
 22
 23  end;
 24  /

Package body created.

SQL>
SQL>
SQL> exec pkg.reset;

PL/SQL procedure successfully completed.

SQL> exec pkg.add(sys.odcinumberlist(1,2,3,4,5,6,7,8,9));

PL/SQL procedure successfully completed.

SQL> exec pkg.add(sys.odcinumberlist(10,20,30,40,50,60,70,80,90));

PL/SQL procedure successfully completed.

SQL> exec pkg.add(sys.odcinumberlist(100,200,300,400,500,600,700,800,900));

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from table(pkg.vals);

COLUMN_VALUE
------------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10
          20
          30
          40
          50
          60
          70
          80
          90
         100
         200
         300
         400
         500
         600
         700
         800
         900

27 rows selected.

SQL>
SQL>
SQL>
SQL>


Thanks for the prompt hlp and reply

Durga, May 24, 2018 - 5:31 am UTC

Thank you so much for responding to my question.

That was my first question on asktom.oracle even though i have been using this site since so many years :)


My input is

Durga, May 25, 2018 - 1:17 pm UTC

Hello Sir,

I have a question about the solution given above -

Part 1:
If i want to use the sys.odcinumberlist solution you have given me, how do i get the input to this package having the sys.odcinumberlist datatype?

How do i convert my values in the IN list into sys.odcinumberlist datatype?

The input values which you have given as
exec pkg.add(sys.odcinumberlist(10,20,30,40,50,60,70,80,90));

In my code, this input list will be dynamically created in a VARCHAR list as
'10,20,30,40,50,60,70,80,90'
along with single quotes
or
(10,20,30,40,50,60,70,80,90)
along with brackets

My input will be a VARCHAR

How can i modify my input so that the pkg.add will be able to process it?

Please suggest.

Part 2:
I have a dynamic IN-list and i have inserted that IN-list into a nested table using a code given in asktom.oracle and it works perfect.
I used this link -
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:110612348061

Original code:
'AND rpsm.PLAN_PLACE_DS_VEH_STATIC_ID IN ' || v_list

v_list is VARCHAR and has value "(1,2,3,4,5...........)"
without the double quotes

New code:
'AND rpsm.PLAN_PLACE_DS_VEH_STATIC_ID IN ' || '(select * from THE(select cast(in_list_tk(v_list) as mytableType ) from dual ) a)'

v_list is of type nested table of NUMBER

The select on nested table runs fine when ran independently

(select * from THE(select cast(in_list_tk(1,2,3,4,5) as mytableType ) from dual ) a)

but when we concatenate that select in the New code, it gives invalid number error.

How to resolve that?
I have tried CAST and TO_CHAR - but didn't help


Please advice.

Thank you for your help and suggestions.

Best regards,
Durga






Connor McDonald
May 28, 2018 - 1:54 am UTC

I can see from your other followup that you have got some code to parse the csv. So rather than INSERT you simply append to the dataytype, eg

CREATE OR REPLACE PROCEDURE setup_in_list (p_in_list IN VARCHAR2) AS 
l_text VARCHAR2(32767) := p_in_list || ','; 
l_idx NUMBER; 
l_element VARCHAR2(32767); 
g sys.odcinumberlist := sys.odcinumberlist()
BEGIN 

LOOP 
l_idx := INSTR(l_text, ','); 
EXIT WHEN NVL(l_idx, 0) = 0; 
l_element := TRIM(SUBSTR(l_text, 1, l_idx - 1)); 
l_text := SUBSTR(l_text, l_idx + 1); 

 g.extend;
 g(g.count) :=l_element 
END LOOP; 
END; 
/


Overcome the 1000 list limitation

Dieter, May 28, 2018 - 9:49 am UTC

WHERE t.a = t1.a1
AND ( (1, t.e) IN ((1, ?), (1, ?), (1, ?), (1, ?)...[5000 more]..... (1,?) ) )

as soon as it is a tupel, the 1000 expressions limitation does not apply.

But, as mentioned earlier, it is most certainly not a good idea to do it this way ...

Dieter
Connor McDonald
May 29, 2018 - 1:54 am UTC

Ugh :-)

In-list with more than 3000 parameters

A reader, June 07, 2019 - 12:29 pm UTC

Hi Connor,

With your kind inputs, I had been able to write this code last year -

This procedure takes IN-list data as its input and inserts it into a GTT (IN_LIST_DATA).

The data in GTT IN_LIST_DATA is queried as a part of select query to return the elements of IN-list.

This procedure has been written to solve the issue -
ORA - 01795: maximum number of expressions in a list 1000
----------------------------------------------------
create or replace PROCEDURE setup_in_list (p_in_list IN VARCHAR2) AS

l_text VARCHAR2(32767) := p_in_list || ',';
l_idx NUMBER;
l_element VARCHAR2(32767);

BEGIN

-- p_in_list is input IN-list
-- deleting elements from GTT in_list_data

DELETE FROM in_list_data;

LOOP
l_idx := INSTR(l_text, ',');
EXIT WHEN NVL(l_idx, 0) = 0;

l_element := TRIM(SUBSTR(l_text, 1, l_idx - 1));
l_text := SUBSTR(l_text, l_idx + 1);

INSERT INTO in_list_data (
num_element)
VALUES (
to_number(trim(l_element)));
END LOOP;

END setup_in_list;

However, the elements i had in the input IN-list are static ids which are 12 digits. If there is a delimiter between them, that is 13 digits per number. So, a VARCHAR2(32767) can hold 32,767 / 13 = 2,730 items in a list.

But my input IN-list has around 6000 records, then how can i change my existing setup_in_list to handle this huge input?

Please advice.

Sincerely,
Durga
Connor McDonald
June 11, 2019 - 5:43 am UTC

Pass it as a clob, and you're good to go

Need good explanation for tuples

DMITRIY, January 06, 2020 - 12:19 pm UTC

Hello!

The question above was Very poorly answered!
Q:
Overcome the 1000 list limitation
May 28, 2018 - 9:49 am UTC
Reviewer: Dieter from Germany
A:
Followup
May 29, 2018 - 1:54 am UTC
Ugh :-)

Pls, explain yourself. What dies this "ugh" mean?
Also give details about optimal solution and maybe some comparisons of performance.
The problem also is with production db permissions on editing global temp and others.

Thank you.
Connor McDonald
January 07, 2020 - 3:18 am UTC

From the original answer, and repeated throughout the thread

"Please do not dyamically create a statement with hard wired IN list values or OR'ed expressions. That is the worst thing you can do to us. We'll spend more time parsing queries then actually executing them!!"

So hacking around it by using tuples might avoid your 1000 limit, but it still just crucifies you on parsing costs. As we said:

"Either use the static temp table or use one of the object tables or PLSQL tables as outlined in the link."

With versions 12.2+ using JSON

Stew Ashton, January 07, 2020 - 11:19 am UTC

With more recent versions (I am told 12.2 patched will work, and I know 18c and 19c work) JSON can be used to split CLOB strings into rows. No need for a GTT or a stored procedure. Performance seems excellent.

- The DATA and CLOB_DATA subqueries are there to create the CLOB.
- The solution is all in the main query.
- JSON_ARRAY does two things: it escapes any special characters and it encloses the string in '[' and ']'. The string becomes a JSON array with one element.
- The REPLACE turns each comma into an element delimiter, so now the JSON array contains many elements.
- Finally, JSON_TABLE unescapes any special characters and returns each element as a separate row.
- Here I am expecting numbers, so I define SUBS as a number and JSON_TABLE does the conversion for me. I add ERROR ON ERROR, otherwise JSON_TABLE will silently discard any non-numbers without telling me!
with data(list) as (
  select listagg(level,',') within group(order by null)
  from dual
  connect by level <= 1000
)
, clob_data(clob_list) as (
  select to_clob(list)
    || ',' || list || ',' || list || ',' || list || ',' || list
    || ',' || list || ',' || list || ',' || list || ',' || list
    || ',' || list || ',' || list || ',' || list || ',' || list
    || ',' || list || ',' || list || ',' || list || ',' || list
  from data
)
select subs from clob_data, json_table(
  replace(json_array(clob_list returning clob), ',', '","'),
  '$[*]' columns (
    subs number path '$'
    error on error
  ) 
);

Happy New Year and best regards,
Stew
Chris Saxon
January 07, 2020 - 2:19 pm UTC

Nice suggestion Stew, thanks for sharing.

Another way used by one of our vendors

Patrick McManus, January 07, 2020 - 2:05 pm UTC

One of our software vendors gets around the 1,000 entry limit for IN (..) expressions by using this construct, allowing for an arbitrarily large number of values:

WHERE t1.x IN (x0..x999)
OR t1.x IN (x1000...x1999)
OR t1.x IN (x2000..x2999)...

It certainly works, whether or not it is more efficient than a global temporary table is another matter. Given the number of concurrent users that would be generating INSERTs and DELETEs against a temporary table (with overlapping values), I doubt that approach would be practical.

Chris Saxon
January 07, 2020 - 2:21 pm UTC

Are those hard-coded values?!

"Concurrency" and GTTs???

Stew Ashton, January 07, 2020 - 7:01 pm UTC

"Given the number of concurrent users that would be generating INSERTs and DELETEs against a temporary table (with overlapping values), I doubt that approach would be practical."

What does that even mean?

Every user (= session) has his own temporary table DATA. Only the definition is global. There is no concurrency whatsoever.

Regards,
Stew
Connor McDonald
January 08, 2020 - 1:02 am UTC

Your guess is as good as mine :-)

Surprise - list of tuples, triples etc. has a limit over 1000

Edgar, June 17, 2020 - 1:14 pm UTC

Taken from
https://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause

select * from t where ('magic',id) in (('magic',1),('magic',2),...,('magic',65535));

Experimentally i found 2^64 is a limit in 12.2.0.1 for the number of tuples in the list. 2^64+1 gives ORA-00913: too many values.

It is totally wrong technic for app developers, but it could be a quick & dirty workaround for someone.
Chris Saxon
June 17, 2020 - 1:41 pm UTC

"Magic" indeed!

Thanks for sharing.

mathguy, August 13, 2021 - 2:13 pm UTC

@Edgar (comment from June 17, 2020)

I tested and the limit for in-list of tuples is 65,535 - as you seem to suggest with your example. Same Oracle version as yours, 12.2.0.1. Please note that 65,535 is 2^16 - 1, not 2^64.

There is no mystery about this limit - see below. Oracle has the 1000 elements limit for IN lists for whatever reason (not very clear to me), but it does not enforce it for tuples of two or more elements. Just making atomic elements into 1-tuples by wrapping them in parentheses doesn't work though; the tuples must be n-tuples with n >= 2.

Regardless, though, IN (list) conditions are expanded as long OR-delimited compound conditions. And Oracle has a hard limit of 2^16 - 1 = 65,535 component conditions for such compound conditions - regardless of what those conditions are (whether they come from an IN list with tuples, or whether they are inequalities, or LIKE conditions, or anything else). Very likely the same limit applies to AND-delimited compound conditions.

SYS.QDCIVARCHAR2LIST

Hans, February 03, 2022 - 6:14 am UTC

According due to my tests replacing

WHERE x IN ('ab','cd') OR IN ('ef','gh')

with

WHERE x IN (SELECT * FROM sys.odcivarchar2list('ab','cd') UNION ALL sys.odcivarchar2list('ef','gh') )

(assuming used after JOINS with big results and long lists) is much faster, but not as fast as creating a temp. table.
Chris Saxon
February 03, 2022 - 6:30 pm UTC

Care to share your complete test?

Code on GitHub

Hans, February 04, 2022 - 12:15 am UTC

https://github.com/hans-helmut/wherein

Connor McDonald
February 04, 2022 - 5:39 am UTC

I think that is going to be a very much case by case basis, and possibly a little due to luck in terms of which is faster or slower. But generally, the optimizer is going to have an easier job costing a query which does not have table functions (nested tables, varrays etc) in it, eg

SQL> create table t as
  2  with letter as (
  3    select chr(ascii('a')+rownum-1) c from dual connect by level <= 26
  4  )
  5  select t1.c||t2.c item, rpad(rownum,100) data
  6  from letter t1, letter t2,
  7    ( select 1 from dual connect by level <= 100 )
  8  /

Table created.

SQL>
SQL> create index ix on t ( item );

Index created.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t
  2  where item in ('ab','cd') or item in ('ef','gh');

Execution Plan
----------------------------------------------------------
Plan hash value: 427062661

---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |   400 | 41600 |    12   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR                     |      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |   400 | 41600 |    12   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IX   |   400 |       |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ITEM"='ab' OR "ITEM"='cd' OR "ITEM"='ef' OR "ITEM"='gh')

SQL>
SQL> select * from t
  2  where item in (select * from sys.odcivarchar2list('ab','cd') union all select * from sys.odcivarchar2list('ef','gh') );

Execution Plan
----------------------------------------------------------
Plan hash value: 2435189465

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |          |  1633K|  3280M|       |  2286   (1)| 00:00:01 |
|*  1 |  HASH JOIN                                |          |  1633K|  3280M|  7664K|  2286   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                       | T        | 67600 |  6865K|       |   287   (1)| 00:00:01 |
|   3 |   VIEW                                    | VW_NSO_1 | 16336 |    31M|       |    58   (0)| 00:00:01 |
|   4 |    HASH UNIQUE                            |          | 16336 | 32672 |       |    58   (0)| 00:00:01 |
|   5 |     UNION-ALL                             |          |       |       |       |            |          |
|   6 |      COLLECTION ITERATOR CONSTRUCTOR FETCH|          |  8168 | 16336 |       |    29   (0)| 00:00:01 |
|   7 |      COLLECTION ITERATOR CONSTRUCTOR FETCH|          |  8168 | 16336 |       |    29   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ITEM"="COLUMN_VALUE")

SQL> set autotrace traceonly stat
SQL> select * from t
  2  where item in ('ab','cd') or item in ('ef','gh');

400 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         70  consistent gets
          4  physical reads
          0  redo size
      49310  bytes sent via SQL*Net to client
        712  bytes received via SQL*Net from client
         28  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        400  rows processed

SQL>
SQL> select * from t
  2  where item in (select * from sys.odcivarchar2list('ab','cd') union all select * from sys.odcivarchar2list('ef','gh') );

400 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1029  consistent gets
          0  physical reads
          0  redo size
      48039  bytes sent via SQL*Net to client
        782  bytes received via SQL*Net from client
         28  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        400  rows processed

SQL> set autotrace off
SQL>

CPU-Time vs. Wall-Time

Hans, February 04, 2022 - 10:21 pm UTC

Thanks again for your time and you patience.

First some clarification: The splitting of the lists and linking them with OR (in case of WHERE IN) or UNION ALL (in case of SYS.ODCIVARCHAR2LIST) is only necessary in case of the list is longer than 1000 items.

It think I found the discrepancy. You are measuring CPU time on the DB server, while I am measuring response times on the client side.
Under high load of the DB-server the results will be different, but for a low loaded server with a user waiting for response it may be faster using SYS.ODCIVARCHAR2LIST.
Connor McDonald
February 07, 2022 - 2:45 am UTC

I should not I'm not discounting any method. As always its not about "what is always best" but what works best for any particular scenario.

How to resolve problem with 1000 limitatations

Saša Petković, February 05, 2022 - 8:41 am UTC

Hi Chriss,

Please pay attention to this example, it may help when reached limit of 1000 items into IN clause.

create table test_where_in (id) as
select level from dual connect by level <= 10000;

if we do next :

select * from test_where_in where id in (1, 2, 3, 4, 5, 6, 7, 8,...1001);

we get an error:

ORA-01795: maximum number of expressions in a list is 1000

To prevent this we can do follow trick:

select * from test_where_in
where (id, 'test') in (
(1, 'test'),
(2, 'test'),
(3, 'test'),
..
(10000, 'test')
);

I have tested with 10000 items in the IN clause and that works fine.

Hope that this trick might help to someone.

Best

Saša Petković
Connor McDonald
February 07, 2022 - 3:03 am UTC

I think this caps out at around 64k, but still a useful trick to have.


This will not use index

Narendra Gupta, February 05, 2022 - 8:56 am UTC

if i have a index on column id , it will not use that index.
Connor McDonald
February 07, 2022 - 2:52 am UTC 

SQL> create table t ( x int primary key, y int );

Table created.

SQL>
SQL> insert into t
  2  select rownum, rownum
  3  from dual
  4  connect by level <= 100;

100 rows created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t
  2  where (x, 'test') in (
  3  (1, 'test'),
  4  (2, 'test'),
  5  (3, 'test')
  6  );

Execution Plan
----------------------------------------------------------
Plan hash value: 3205741637

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     3 |    18 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |              |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T            |     3 |    18 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C0066638 |     3 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("X"=1 OR "X"=2 OR "X"=3)

SQL>
SQL> set autotrace off


CPU-Time on Oracle user

Hans, February 05, 2022 - 10:05 pm UTC

Hello again!

I created queries with a list of length 20000, having items with the length of 32.

Running this the first time (query caching) brings the CPU-usage of the database-server on 100% for a few seconds (single process "oracle_7806_xe", observed with Linux "top") using "WHERE IN" only, but only to about 15% for a short time using "SYS.ODCIVARCHAR2LIST".

The statistics look the other way round, like yours...

In the case two tables are joined before, the differences are even greater.


Connor McDonald
February 07, 2022 - 3:13 am UTC

My concern is that once you start building giant SQL's, then the cost of parsing them can often outweigh anything else, eg on this simple example below, I drop 0.50seconds just parsing the SQL

SQL> create table t ( x int primary key, y int );

Table created.

SQL> insert into t
  2  select rownum, rownum
  3  from dual
  4  connect by level <= 100;

100 rows created.

SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

SQL> create table t1 as select rownum z from dual connect by level <= 3000;

Table created.


select * from t
where (x, 0) in (
(1,0),
(2,0),
(3,0),
(4,0),
(5,0),
(6,0),
(7,0),
(8,0),
(9,0),
...
(2997,0),
(2998,0),
(2999,0),
(3000,0)
)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.06       0.51          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        8      0.00       0.00          0         13          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.06       0.51          0         13          0         100


select t.*
from t, t1
where t.x = t1.z

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          1           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        8      0.00       0.00          5        123          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.00       0.00          5        123          1         100


Example SQLs

Hans, February 06, 2022 - 9:49 pm UTC

Hello,
for better testing, I wrote a Java program to generate the statements:
https://github.com/hans-helmut/wherein/tree/main/testsql

I could not upload the statement for creating the tables, as it is over 400 MB.
To get clear results, I had to join two tables first. All tests where run with 1000 statements containing a list of 200 each. The total runtimes where 16 vs 32 seconds, sqlplus needs 8 seconds, assuming both processes wait for each other, the DB server needs 8 vs 24 seconds.
Looking at "top", the DB server needs 35% vs 70 % CPU for the double time. So using SYS.ODCIVARCHAR2LIST it is 3 to 4 times faster in this test.

Maybe someone can confirm this results. In my opinion the estimation of the costs is not correct (as far as I can understand them)


Tuple List works perfectly fine

Jeff, October 24, 2023 - 9:51 pm UTC

I get why you wouldn't want to be doing these queries every second of every day, but what is the harm when it is a once a month update? Honestly, the advice in here from the professionals is so dogmatic they can't even see the forest for the trees. You want to criticize developers who don't want to waste their time making temporary tables or whatever other solutions to overcome oracles seemingly arbitrary 1000 item limitation, easily overcome by using tuples. Oracle RDBMS is a multimillion line POS controlled by innumerable flags. Seriously, get off your high horse and just provide a solution instead of pontificating.
Chris Saxon
October 26, 2023 - 1:12 pm UTC

The limit is much higher in 23c:

create table t ( c1 int );
  
select * from t
where c1 in (
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,
528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000,
<snip>,
65001,65002,65003,65004,65005,65006,65007,65008,65009,65010,65011,65012,65013,65014,65015,65016,65017,65018,65019,65020,65021,65022,65023,65024,65025,65026,65027,65028,65029,65030,65031,65032,65033,65034,65035,65036,65037,65038,65039,65040,65041,65042,65043,65044,65045,65046,65047,65048,65049,65050,65051,65052,65053,65054,65055,65056,65057,65058,65059,65060,65061,65062,65063,65064,65065,65066,65067,65068,65069,65070,65071,65072,65073,65074,65075,65076,65077,65078,65079,65080,65081,65082,65083,65084,65085,65086,65087,65088,65089,65090,65091,65092,65093,65094,65095,65096,65097,65098,65099,65100,65101,65102,65103,65104,65105,65106,65107,65108,65109,65110,65111,65112,65113,65114,65115,65116,65117,65118,65119,65120,65121,65122,65123,65124,65125,65126,65127,65128,65129,65130,65131,65132,65133,65134,65135,65136,65137,65138,65139,65140,65141,65142,65143,65144,65145,65146,65147,65148,65149,65150,65151,65152,65153,65154,65155,65156,65157,65158,65159,65160,65161,65162,65163,65164,65165,65166,65167,65168,65169,65170,65171,65172,65173,65174,65175,65176,65177,65178,65179,65180,65181,65182,65183,65184,65185,65186,65187,65188,65189,65190,65191,65192,65193,65194,65195,65196,65197,65198,65199,65200,65201,65202,65203,65204,65205,65206,65207,65208,65209,65210,65211,65212,65213,65214,65215,65216,65217,65218,65219,65220,65221,65222,65223,65224,65225,65226,65227,65228,65229,65230,65231,65232,65233,65234,65235,65236,65237,65238,65239,65240,65241,65242,65243,65244,65245,65246,65247,65248,65249,65250,65251,65252,65253,65254,65255,65256,65257,65258,65259,65260,65261,65262,65263,65264,65265,65266,65267,65268,65269,65270,65271,65272,65273,65274,65275,65276,65277,65278,65279,65280,65281,65282,65283,65284,65285,65286,65287,65288,65289,65290,65291,65292,65293,65294,65295,65296,65297,65298,65299,65300,65301,65302,65303,65304,65305,65306,65307,65308,65309,65310,65311,65312,65313,65314,65315,65316,65317,65318,65319,65320,65321,65322,65323,65324,65325,65326,65327,65328,65329,65330,65331,65332,65333,65
334,65335,65336,65337,65338,65339,65340,65341,65342,65343,65344,65345,65346,65347,65348,65349,65350,65351,65352,65353,65354,65355,65356,65357,65358,65359,65360,65361,65362,65363,65364,65365,65366,65367,65368,65369,65370,65371,65372,65373,65374,65375,65376,65377,65378,65379,65380,65381,65382,65383,65384,65385,65386,65387,65388,65389,65390,65391,65392,65393,65394,65395,65396,65397,65398,65399,65400,65401,65402,65403,65404,65405,65406,65407,65408,65409,65410,65411,65412,65413,65414,65415,65416,65417,65418,65419,65420,65421,65422,65423,65424,65425,65426,65427,65428,65429,65430,65431,65432,65433,65434,65435,65436,65437,65438,65439,65440,65441,65442,65443,65444,65445,65446,65447,65448,65449,65450,65451,65452,65453,65454,65455,65456,65457,65458,65459,65460,65461,65462,65463,65464,65465,65466,65467,65468,65469,65470,65471,65472,65473,65474,65475,65476,65477,65478,65479,65480,65481,65482,65483,65484,65485,65486,65487,65488,65489,65490,65491,65492,65493,65494,65495,65496,65497,65498,65499,65500,65501,65502,65503,65504,65505,65506,65507,65508,65509,65510,65511,65512,65513,65514,65515,65516,65517,65518,65519,65520,65521,65522,65523,65524,65525,65526,65527,65528,65529,65530,65531,65532,65533,65534,65535,65536,65537,65538,65539,65540,65541,65542,65543,65544,65545,65546,65547,65548,65549,65550,65551,65552,65553,65554,65555,65556,65557,65558,65559,65560,65561,65562,65563,65564,65565,65566,65567,65568,65569,65570,65571,65572,65573,65574,65575,65576,65577,65578,65579,65580,65581,65582,65583,65584,65585,65586,65587,65588,65589,65590,65591,65592,65593,65594,65595,65596,65597,65598,65599,65600,65601,65602,65603,65604,65605,65606,65607,65608,65609,65610,65611,65612,65613,65614,65615,65616,65617,65618,65619,65620,65621,65622,65623,65624,65625,65626,65627,65628,65629,65630,65631,65632,65633,65634,65635,65636,65637,65638,65639,65640,65641,65642,65643,65644,65645,65646,65647,65648,65649,65650,65651,65652,65653,65654,65655,65656,65657,65658,65659,65660,65661,65662,65663,65664,65665,65666,6566
7,65668,65669,65670,65671,65672,65673,65674,65675,65676,65677,65678,65679,65680,65681,65682,65683,65684,65685,65686,65687,65688,65689,65690,65691,65692,65693,65694,65695,65696,65697,65698,65699,65700,65701,65702,65703,65704,65705,65706,65707,65708,65709,65710,65711,65712,65713,65714,65715,65716,65717,65718,65719,65720,65721,65722,65723,65724,65725,65726,65727,65728,65729,65730,65731,65732,65733,65734,65735,65736,65737,65738,65739,65740,65741,65742,65743,65744,65745,65746,65747,65748,65749,65750,65751,65752,65753,65754,65755,65756,65757,65758,65759,65760,65761,65762,65763,65764,65765,65766,65767,65768,65769,65770,65771,65772,65773,65774,65775,65776,65777,65778,65779,65780,65781,65782,65783,65784,65785,65786,65787,65788,65789,65790,65791,65792,65793,65794,65795,65796,65797,65798,65799,65800,65801,65802,65803,65804,65805,65806,65807,65808,65809,65810,65811,65812,65813,65814,65815,65816,65817,65818,65819,65820,65821,65822,65823,65824,65825,65826,65827,65828,65829,65830,65831,65832,65833,65834,65835,65836,65837,65838,65839,65840,65841,65842,65843,65844,65845,65846,65847,65848,65849,65850,65851,65852,65853,65854,65855,65856,65857,65858,65859,65860,65861,65862,65863,65864,65865,65866,65867,65868,65869,65870,65871,65872,65873,65874,65875,65876,65877,65878,65879,65880,65881,65882,65883,65884,65885,65886,65887,65888,65889,65890,65891,65892,65893,65894,65895,65896,65897,65898,65899,65900,65901,65902,65903,65904,65905,65906,65907,65908,65909,65910,65911,65912,65913,65914,65915,65916,65917,65918,65919,65920,65921,65922,65923,65924,65925,65926,65927,65928,65929,65930,65931,65932,65933,65934,65935,65936,65937,65938,65939,65940,65941,65942,65943,65944,65945,65946,65947,65948,65949,65950,65951,65952,65953,65954,65955,65956,65957,65958,65959,65960,65961,65962,65963,65964,65965,65966,65967,65968,65969,65970,65971,65972,65973,65974,65975,65976,65977,65978,65979,65980,65981,65982,65983,65984,65985,65986,65987,65988,65989,65990,65991,65992,65993,65994,65995,65996,65997,65998,65999,66000
);

ORA-01795: maximum number of expressions in a list is 65535

New limit for expression lists in Oracle 23

mathguy, October 26, 2023 - 7:30 pm UTC

The limit is much higher in 23c:

This is great, thank you for the info! This is easily missed, since the documentation has not been updated:

https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Expression-Lists.html#GUID-5CC8FC75-813B-44AA-8737-D940FA887D1E

A comma-delimited list of expressions can contain no more than 1000 expressions.
Chris Saxon
October 27, 2023 - 12:39 pm UTC

You're welcome, thanks for pointing out the doc issue; we'll get it updated.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.