ORA-01795 Oracle in中超过1000个值处理方法汇总

149 篇文章 21 订阅
51 篇文章 1 订阅

       最近又又又遇到了开发同事问Oracle 中in超过1000个值遇到ORA-01795: maximum number of expressions in a list is 1000怎么处理,之前也陆陆续续查过一些方法,汇总整理一把。当然,其中的一些方法只是保证它不报错,性能可能堪忧,尽量少用。

select * from table1 where ID in (1,2,3,4,...,1001,1002,...);

一、 拆分/拼接SQL

1. 拆分循环执行

       例如有10000个值要in,就拆成10个20个语句循环执行,然后再处理结果。如果能拆得比较小,SQL能走上索引,其实还可以;如果拆出来都是大表走全表扫描,那就窒息了。

       还遇到过有种极致的拆法:拆成一条条ID=xxx去执行,如果数据量大,通常性能不如小批量的in,还是需要测试出一个合适的值。

2. or拼接SQL

select * from table1 where ID in (1,2,3,4,...,1000) or ID in (1001,1002,...,2000)

思路简单,但要对代码做一定改造,另外如果拼得太长,解析耗时和内存占用也够呛。

3. union all

select * from table1 where ID in (1,2,3,4,...,1000)
union all
select * from table1 where ID in (1001,1002,...)

优缺点跟上面类似

二、 使用临时表关联

1. 可以直接改为关联

       有些神奇的程序从某个/些表里查出来一堆结果,存到列表里,再放到另一个表的in中。相对来说这是最好改的,通常直接改为表关联,或者将结果插入临时表后进行关联即可。

select where id in (select id from temptable);

2. 构造临时表

       还遇到过in中的数据是从缓存中取出、或者程序构造的,并不在DB的表里,就需要先构造。可以是直接insert,也可以利用变量+CTE,例如:

var b1 varchar2(2000);
exec :b1:='1,2,3,…,1002';

with str_list as
(select cast(REGEXP_SUBSTR( :b1,'[^,]+', 1, level) as number) as value
from dual
connect by level <= regexp_count(:b1, '[^,]+')
)
SELECT object_name FROM t1
where t1.object_id in (select value from str_list);

当然,如果in中的字符很长,很可能变量长度hold不住。

三、 多行多列子查询

       名字叫Multiple Row and Column Subqueries,使用这种语法,in中值的上限可以到10万而不是1000,足够满足绝大多数业务场景。当然,如果真的in 10万个值,性能绝对够呛,慎用…

select column_X, ... from my_table
where ('magic', column_X ) in (
        ('magic', 1),
        ('magic', 2),
        ('magic', 3),
        ('magic', 4),
             ...
        ('magic', 99999)
    )

简单例子

with grades as (
  select 'Jim' usr, 'B' grade from dual
  union all
  select 'Bill', 'C' from dual
  union all
  select 'Tim', 'A' from dual
  union all
  select 'Jim', 'B+' from dual
)
select *
  from grades
 where (usr,grade) in (('Jim','B'),
                       ('Tim','C'),
                       ('Tim','A'));

后面如果学到新方法,再继续补充了~

参考

How to put more than 1000 values into an Oracle IN clause - Stack Overflow

Is it possible to compare tuples in oracle-compatible sql? - Stack Overflow

《专题培训-SQL写法与改写》

### 回答1: ORA-01795Oracle数据库的错误代码,它表示在一个SQL语句,列表最多只能包含100个表达式。这个错误通常发生在使用IN子句时,IN子句包含的值太多,超过了100个。解决这个问题的方法是将IN子句的值分成多个小的子集,或者使用其他方法来代替IN子句。 ### 回答2: ORA-01795Oracle数据库错误代码之一,表示在SQL语句的IN操作符引用的表达式数超过了最大限制,最大限制为1000。 IN操作符是用于在SQL查询指定多个可能的值的运算符,它可以在WHERE子句用于限制结果集的返回。在IN操作符,可以列举多个值,或者使用子查询从另一个表获取多个值。 但是,在Oracle数据库,IN操作符的表达式数目是有限制的。如果在一个IN操作符引用的表达式数超过1000个,就会触发ORA-01795错误。这个错误的出现通常是因为应用程序不当地使用了IN操作符,在查询过程指定了太多的条件。 为了解决ORA-01795错误,应用程序需要重新考虑使用IN操作符的方式。在SQL查询,可以使用其他操作符替代IN操作符,比如NOT IN、EXISTS、或者使用多个IN操作符,将查询条件分割成多个分段。 除了重新规划SQL查询外,还可以通过增加最大限制值的方式来解决ORA-01795错误。在Oracle数据库可以使用ALTER SYSTEM语句修改最大表达式数的值,但是增大值可能会导致数据库性能下降,因此需要谨慎考虑。同时,在修改过程,也需要注意遵循Oracle官方文档对应的最大限制值。 ### 回答3: ORA-01795Oracle的一个错误代码,它的意思是在一个SQL语句出现了超过1000个表达式,这是Oracle数据库引擎的一个限制,超过这个限制会导致查询失败。 在Oracle数据库,一个SQL语句的表达式数量是有限制的,这个限制是1000个。当一个查询语句出现了超过1000个表达式时,就会触发ORA-01795错误码。这个错误码通常出现在复杂的查询语句,比如当查询某个表的大量数据时,或者使用了大量的函数、或者子查询等语法。 解决这个错误的方法是需要优化SQL语句,例如通过使用分页或缓存等技术来减小查询的结果集大小,或者通过精简语句的函数、列等来减少表达式数量。另外,如果实在无法避免出现这个错误,那么可以通过增加Oracle数据库参数的值来增加表达式数量的上限,但这种做法并不推荐,因为它可能会对数据库的性能产生不利影响。 总之,ORA-01795错误码是Oracle数据库一个常见的错误,它表明查询语句出现了过多的表达式。要解决这个问题,需要优化SQL语句,避免使用过多的函数、列、子查询等语法,或者调整数据库参数来增加表达式数量的上限。了解这个错误码对于有效调整Oracle数据库的性能非常重要。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Hehuyi_In

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值