oracle sql in 超过1000个参数报错(ORA-01795: 列表中的最大表达式数为 1000)解决办法之一

        在oracle数据库中，sql使用in时，如果in的能数超过1000就会报出"ORA-01795: 列表中的最大表达式数为 1000/ORA-01795: maximum number of expressions in a list is 1000"的错误,这个也是生产中大家常踩的坑之一！

       在测试环境下，因为数据量小，测试时用例没有覆盖到。有些场景，大家在评估的时间会觉得正常情况下肯定不会超1000（想想墨菲定律），往往在这个时间就已经埋下了暗雷！

    网上关于ORA-01795的解决办法也有很多，比如 使用 in() or in()，创建临时表join，使用union 等等，我这里也提供一个解决办法，处理办法没有标准至于选哪种看自己的喜好和应用场景。

 

官方说：A comma-delimited list of expressions can contain no more than 1000 expressions. A comma-delimited list of sets of expressions can contain any number of sets, but each set can contain no more than 1000 expressions.

        这里使用oracle tuple（ A comma-delimited list of sets of expressions） 也就是元组,语法如下：

SELECT * FROM TABLE_NAME WHERE (1, COLUMN_NAME) IN 
((1, VALUE_1), 
(1, VALUE_2), 
...
...
...
...
(1, VALUE_1000),
(1, VALUE_1001));

比如我们想要从用户表里通过用户id 查询用户信息可以这样写：

select * from t_user u where (1, u.id) in ((1, 'id001'),(1,'id002'),(1,'XXX'))

上面的语句其实等同于：select * from t_user u where (1=1 and u.id='id001') or (1=1 and u.id='id002') or (1=1 and u.id='XXX')

大家的工程多数会用ORM框架如MyBatis 我们可以借助MyBatis的foreach 原来是这写:

 WHERE u.id IN(
        <foreach collection="list" item="item" index="index" separator=",">
            #{item}
        </foreach>
        )

现在稍稍修改一下：

 WHERE (1, u.id) IN(
        <foreach collection="list" item="item" index="index" separator=",">
            (1, #{item})
        </foreach>
        )

个人建议：这个不是最好的办法，如果数据量大的情况下，还是推荐大家做分页处理，如果数据量很大的话更加推荐数据库分页，我这里也有一篇内存分页的文章，如果有需要大家可以参考：利用java8 stream api 实现List集合分页获取工具

另外这里也提醒我们的测试人员，在一些批量处理功能测试时，最好用例在设计的时候考虑大量数据处理的case！

参考文档：https://docs.oracle.com/database/121/SQLRF/expressions016.htm#SQLRF52099

                  http://dbaparadise.com/2016/07/limitations-of-the-in-clause-in-oracle-ora-01795/#3

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