In Oracle we can only put up to 1000 values into an IN clause. I would like to see this restriction dropped.
Sometimes it happens that the business departments asks me to "report all items not online for this list of 2500 item numbers" and I'll have to use an editor to split the numbers into chunks of 1000 and do
select * from items where status <> 'online' and itemno in ( <first 1000 item numbers> ) or itemno in ( <next 1000 item numbers> ) or itemno in ( <last 500 item numbers> );
I've never really understood why this limit even exists. Some people argue that it would be better to create a table with the numbers and use this, because an IN clause on many values is slow. What the heck? I am not supposed to even know about this. If Oracle's optimizer sees it appropriate to work with some temporary table let it do so. With SQL I am supposed to tell the DBMS what to do, not what method to apply to achieve it. And what the DBMS shall do is:
select * from items where status <> 'online' and itemno in ( <2500 item numbers> );