如何用Java代码解析ORA-01795 [英] How to resolve ORA-01795 in Java code

问题描述

在IN子句中执行1000条以上的记录时，我的Java代码出现ORA-01795错误. 我正在考虑使用多个由OR子句分隔的IN子句在1000个条目的批处理中将其打破，如下所示:

I am getting ORA-01795 error in my Java code while executing more than 1000 records in IN clause. I am thinking to break it in the batch of 1000 entries using multiple IN clause separated by OR clause like below:

  select * from table_name
  where
  column_name in (V1,V2,V3,...V1000)
  or
  column_name in (V1001,V1002,V1003,...V2000)

我有一个像-18435,16690,1719,1082,1026,100759...这样的字符串ID，它是根据用户选择动态生成的.如何在Java中针对1-1000条记录，1001至2000条记录等条件编写逻辑.有人可以在这里帮助我吗?

I have a string id's like -18435,16690,1719,1082,1026,100759... which gets generated dynamically based on user selection. How to write a logic for condition like 1-1000 records ,1001 to 2000 records etc in Java. Can anyone help me here?

推荐答案

有三种可能的方法可以解决此限制:

There are three potential ways around this limit:

1)正如您已经提到的:将语句分成1000批

1) As you have already mentioned: split up the statement in batches of 1000

2)使用这些值创建一个派生表，然后将它们联接:

2) Create a derived table using the values and then join them:

with id_list (id) as (
  select 'V1' from dual union all
  select 'V2' from dual union all
  select 'V3' from dual
)
select *
from the_table
where column_name in (select id from id_list);

或者，您也可以加入这些值-甚至可能更快:

alternatively you could also join those values - might even be faster:

with id_list (id) as (
  select 'V1' from dual union all
  select 'V2' from dual union all
  select 'V3' from dual
)
select t.*
from the_table t
  join id_list l on t.column_name = l.id;

这仍然会生成一个非常非常大的语句，但没有1000个ID的限制.我不确定Oracle将以多快的速度解析它.

This still generates a really, really huge statement, but doesn't have the limit of 1000 ids. I'm not sure how fast Oracle will parse this though.

3)将值插入(全局)临时表中，然后使用IN子句(或JOIN).这可能是最快的解决方案.

3) Insert the values into a (global) temporary table and then use an IN clause (or a JOIN). This is probably going to be the fastest solution.

这篇关于如何用Java代码解析ORA-01795的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！