应用结果集的提取大小会杀死程序

Question

这对我来说是一个非常有趣的问题，希望你能帮我解决。我正在查询以从 Oracle 数据库表中选择所有行。使用了oracle jdbc驱动程序。为避免连接超时，使用 rownum 以 100 行为增量执行查询。一切都会好起来的，但是程序冻结在结果集的第 91 行，试图执行 resultSet.next()。其中没有任何例外。我试图寻找这种行为的原因，并意识到问题在于结果集的获取大小。提取大小的默认值为 10。此行为看起来像是从结果集中拉出这 10 行，并且当我们进入释放的空间时程序被冻结。然后我们设置抓取大小 0，一切正常。这是预期的行为吗？如果是这样，为什么？在下面的示例中，通过按行号退出循环来绕过此问题。

private static volatile int bottomRow = -99;
private static volatile int topRow = 0;
private static final String SQL = "SELECT * from (select m.*, rownum r from keyspace.table m) where r >= ? and r < ?";
public static void select(Connection connection) {
try (PreparedStatement preparedStatement=connection.prepareStatement(SQL)){
while (true) {
incrementCounters();
preparedStatement.setInt(1, bottomRow);
preparedStatement.setInt(2, topRow);
ResultSet rs = preparedStatement.executeQuery();
// rs.getFetchSize(); -> default value is 10
if (rs.next()) {
do {
rs.getString("id");
rs.getString("customer_name");
/* some logic */
if (rs.getRow() == 90) {
break;
}
} while (rs.next());
} else break;
}
} catch (Exception e) {
} 
}
private synchronized static void incrementCounters() {
Thread.sleep(700);
if (topRow != 0) {
bottomRow += 90;
topRow = bottomRow + 100;
} else {
bottomRow += 100;
topRow += 100;
}
}

JDBC 驱动程序版本

<dependency>
<groupId>com.oracle</groupId>
<artifactId>jdbc</artifactId>
<version>11.2.0.3</version>
<type>pom</type>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.3</version>
</dependency>

池属性的配置

private static DataSource ds=null;
public static Connection getConnection() throws SQLException{
if (ds==null){
synchronized (Source.class.getName()) {
if (ds==null)
try {
DriverManager.setLoginTimeout(1);
String driverClassName="oracle.jdbc.OracleDriver";
PoolProperties p = new PoolProperties();
p.setUrl(url);
p.setDriverClassName(driverClassName);
p.setUsername(username);
p.setPassword(password);
p.setJmxEnabled(false);
p.setTestWhileIdle(false);
p.setTestOnBorrow(true);
p.setValidationQuery("SELECT 1 from dual");
p.setTestOnReturn(false);
p.setTestOnConnect(false);
p.setValidationInterval(5*1000);
p.setTimeBetweenEvictionRunsMillis(120000);
p.setMaxActive(500);
p.setInitialSize(0);
p.setMinIdle(30);
p.setMaxIdle(100);
p.setRemoveAbandonedTimeout(60);
p.setMinEvictableIdleTimeMillis(120000);
p.setLogAbandoned(false);
p.setRemoveAbandoned(true);
p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState; org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer; org.apache.tomcat.jdbc.pool.interceptor.StatementCache");
ds = new DataSource();
ds.setPoolProperties(p);
} catch (Exception e) {
log.error("error {}",e.getMessage());
throw new RuntimeException(e);
}
}
}
return ds.getConnection();
}

Answer 1

我真的无法回答你的问题，因为缺少很多细节，但我必须在这里指出的一件事是，代码似乎不必要地过分了，这可能是它看似错误行为的原因。

如果您要选择所有行(或运行具有条件的选择)，我建议您使用一个简单的select * from [table]并将其余部分省略。

您必须了解的是，JDBC 驱动程序处理的不仅仅是查询编译和数据传输，而且过于复杂的查询会阻止它优化您正在执行的任何操作。

此外，请记住，使用边界运行多个选择会产生大量的开销，特别是在 Oracle 上，即使有良好的索引，也并不总是能成功。

例如，运行查询以获取 1000 条记录会创建一个结果集，打开一个流并查找记录，筛选它们，对它们进行排序，以 JDBC 驱动程序认为最有意义的任何批大小传输记录。(在数据库端，只有一个指针从第一个移动到最后一个)

运行同样的事情，但用硬编码的 100 条记录大小运行 10 次会产生开销，需要创建 10 个结果集，数据库服务器需要查找记录 10 次，过滤 10 次，对它们进行 10 次排序，然后跳过适当的数量以到达您请求的批处理，这意味着 0，然后是 100， 然后200...(在数据库端，每个批次都必须创建一个新指针，然后将其移动到适当的位置，然后才能开始传输)

现在，对于 1000 条记录来说，这并不重要，但始终编写弹性代码是一种很好的做法，但是如果你必须在一个有 2000 万条记录的表上这样做(我已经做到了，这就是我学到的)，工作从(在我的情况下)几分钟到几周。

至于超时，它们不是问题，除非处理您已经传输的数据需要太长时间，但如果是这种情况，我不会考虑查询优化，而是数据处理一旦已经进入(也许引入某种形式的并行以同时完成更多工作)。

如果针对您的具体情况，您可以提供更多详细信息，例如表中存储的内容、有多少条记录等，我很乐意更新我的答案。

希望有帮助。

Answer 2

"为了避免连接超时，使用 rownum 以 100 行为增量执行查询">

您可以首先检查连接超时的原因。例如：当您尝试在 sqldeveloper 或 toad 中运行查询时，您是否遇到超时。

查询"从 (select m.*， rownum r from keyspace.table m)>= 在哪里？和 r <？" 看起来您希望实现等效分页。我建议，如果您有兴趣获取要在 UI 上显示的批量数据，您希望按内部查询对确定性记录集进行排序。

例如：它应该是 SELECT * from (select m.*， rownum r from keyspace.table m ORDER BY )>= ？ 和 r <？。

如果您在 12c 及以上，请查看行限制条款的选项 https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1