Thursday, February 19, 2015

How Many Values Can You Have in an IN List?

I have often wondered how many values you could have following an IN and I have just found out. I loaded some new data into a name and address table in an Oracle 11 database over the weekend. On Monday, a user sent me an Excel spreadsheet containing a list of almost 18000 meter point references to search for in the table. I exported them into a file, copied it to the server and used vi to add a comma at the end of each line. Then I added a query at the start of the data:

select * from m_number_detail
where mpo_reference in (
0002538404,
0002538505,
0001312900,
0001313408,
0006175302,
Etc
Etc

When I tried to run it, I saw the following message at the end of the SPOOL file:

Etc
Etc
17906  8815785700,
17907  8817723609,
17908  0072096508,
17909  0072096710,
17910  0925482304)
17911  /
0005567909,
*
ERROR at line 1003:
ORA-01795: maximum number of expressions in a list is 1000
 
SQL>

2 comments:

Unknown said...

Andrew, always enjoy your blog posts.

Just to reinforce what you've indicated, the documentation mentions the 1000 limit also (IN Condition).

Andrew Reid said...

Thank you for your support. I shouldn't say this but I don't always read the documentation!