看ORA-01795中给出的内容是SQL语句的 expressions 中list接受的最大值是1000,查了下ORA-01795的说明,确定问题出在IN后括号里的主键值超过1000上
解决思路:将 SQL 中超过1000个的in list转换为 id in (1,2,3...999) or id in (1000,1001....1999) ...
或者考虑换成exist 语句,对于后表比前表 大数据量的情况时 ,用exist性能也有提升。
解决思路:将 SQL 中超过1000个的in list转换为 id in (1,2,3...999) or id in (1000,1001....1999) ...
或者考虑换成exist 语句,对于后表比前表 大数据量的情况时 ,用exist性能也有提升。
StringBuffer sb = new StringBuffer();
for (int i = 0; i < columnContents.size(); i++) {
if (PluginConst.EMPTY_STRING.equals(columnContents.get(i))) {
continue;
}
if ((i % 1000) == 0 && i > 0) {
sb.deleteCharAt(sb.length() - 1);
sb.append(") OR " + columnName + " IN ( '"
+ columnContents.get(i) + "',"); // resolved
// resolved ORA-01795 problem.
} else {
sb.append("'" + columnContents.get(i) + "',");
}
}
// delete the last comma
sb.deleteCharAt(sb.length() - 1);
String selectSQL = "SELECT * FROM " + tableName + " WHERE "
+ columnName + " IN ( " + sb.toString() + " )";