Limitations Of The IN Clause In Oracle – ORA-01795

Did you ever get the error “ORA-01795 maximum number of expressions in a list is 1000“?
I didn’t. Truth be told, I didn’t even know this error existed, until recently, when I wrote a query, and got the error. I don’t recall learning about this limitation for the IN clause when I was in DBA training, however, this got me thinking, it could make a pretty good OCA/OCP question.

Now that I made you curious, let me answer some questions you might have (I know I did):

1. What Is ORA-01795?
2. Why Would You Use 1000+ values in the IN clause?
3. Workaround for ORA-01795.

Before I continue on, let me tell you the story, of how I got this error. There was a database job that deleted data from table partitions for various tables. The job was taking a long time. The developer who created it, came for advice, and I recommended to save the data in each partition, outside the table (only 1% of the data was to be kept, and 99% of it to be deleted), truncate the partition, and then insert the data back into the table.

Truncating the partition would be much faster operation than a delete, and as a bonus would release the space in the database. These being old partitions, there was no new data being inserted, and the partitions would not grow in size again.

For reporting purposes, and out of my curiosity, I recommended capturing the partition size prior to the cleanup job, and after, to see how much space is saved. The developer provided the list of partitions to be truncated, this list was over 1000 partitions (Crazy, I know).

When I wrote my query to get the size of each partition, I got this error: “ORA-01795 maximum number of expressions in a list is 1000”. The query looked like this:

select a.segment_name, sum(bytes)/1024/1024 size_MB
from dba_segments a, dba_tab_subpartitions b
where a.segment_name='ERROR_LOG'
and a.partition_name=b.subpartition_name
and b.table_name='ERROR_LOG'
and b.subpartition_name in
('P0101_1'
,'P0101_2'
...          --over 1000 expressions
,'P0101_1200'
)
group by segment_name
order by segment_name;

1.What is ORA-01795?

If you search for the error on Oracle’s website, this is what you find:

ORA-01795: maximum number of expressions in a list is 1000
Cause: Number of expressions in the query exceeded than 1000. Note that unused column/expressions are also counted Maximum number of expressions that are allowed are 1000.
Action: Reduce the number of expressions in the list and resubmit.

You receive this error when you attempt to use more than 1000 expressions, or literals in the IN clause, just like I did:

column in (‘1′,’2′,…’1200’)

2.Why Would You Use 1000+ values in the IN clause?

I wasn’t aware of the 1000 limitation. Writing this post, I got curious if this limitation/behaviour can be found in Oracle’s documentation. It took me a while to find it. As of this writing, you can read about it here http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions014.htm#i1033664.

Why would you have 1000+ values in the IN clause?

• Because you actually have a list of 1000+ values to compare it with, just as I had a list of over 1000 partitions.
• The application you are running might be generating these queries on the fly, and have 1000+ values in the list.
  I actually came across a forum post, where a user was asking for help, because the IN clause would have over 1 million values. That application probably needs a makeover.

3.Workaround for ORA-01795.

As the 1000 value is a limitation in Oracle, you need a workaround for that. Depending on how the query is run, there are a few workarounds.

• If you run the query on demand, occasionally, as I did. My query will not be part of a scheduled recurring job. I just run it once in a while, thus I only need a quick fix for it:
  

  Split up the list into 1000 item chunks, such as:

  select a.segment_name, sum(bytes)/1024/1024 size_MB
  from dba_segments a, dba_tab_subpartitions b
  where a.segment_name='ERROR_LOG'
  and a.partition_name=b.subpartition_name
  and b.table_name='ERROR_LOG'
  and (
  b.subpartition_name in
  ('P0101_1'
  ...             -- 1000 values
  ,'P0101_1000')
  or
  b.subpartition_name in
  ('P0101_1001'
  ...             -- rest of the values     
  ,'P0101_1200')
  )
  group by segment_name
  order by segment_name;
  

  Use Tuples, as there are no limitations for Tuples. Oracle allows more than 1000 values for tuples:

  SELECT col1, col2, col3
  FROM table
  WHERE (1, col1) IN ((1, value1), (1, value2), (1, value3),.....(1, value1200)); 
  

• If you run the query in the application, and you need to run it often, then definitely Tom Kyte’s solution is the best. Basically he recommends the following:
  

  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 );”

These are the workarounds for the 1000 limit on the IN clause expression, that I put together. I am sure that there are more workarounds out there. Now you know what to do, if you come across this error!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

-Diana

Share this:

• Facebook
• X