如何在Oracle中全局限制结果集（或按会话）？

Question

我希望能够在不必更改应用程序中的每个SQL语句的情况下限制结果集的大小（至少在某些情况下）。因此，在每个查询中添加ROWNUM子句不是一个选项。

我正在寻找一些全局参数，或者至少是会话参数（例如SQLServer中的SET ROWCOUNT）。

该应用程序使用JDBC资源池连接到Oracle，因此我可以在SQL init JDBC池中设置会话级别的参数。

- JayCBee

Answer 1

你尝试过使用Statement.setMaxRows方法吗？这是一种标准的JDBC方法，它会在获取指定最大行数后静默地删除多余的行。

然而，从性能角度考虑，实际上修改发送到数据库的查询可能更好。如果像@MK建议的那样操作，您将告诉优化器您只需要获取N行，这可能有助于优化器选择更高效的计划。

- Justin Cave

---

对于这个问题，有两个主要的难点：a）数据库仍然需要查找所有行，只是 JDBC 将其删除了；b）他必须手动查找执行查询的每个地方。 - MK.

@MK - Oracle通常只在抓取结果时才实体化它们（当然，必须获取所有数据以便对其进行排序以返回第一行的情况除外）。因此，如果您从未获取超过前N行，则通常可以节省执行时间。当然，如果您使用ROWNUM子句告诉Oracle您只想要前N行，则可以节省的时间要多得多。理论上，JDBC驱动程序可以通过在默默添加“WHERE rownum <= N”子句来实现setMaxRows--不知道Oracle JDBC驱动程序是否实际这样做。 - Justin Cave

有趣。我确实见过人们在Oracle查询中手动添加WHERE rownum限制，除了setMaxRows之外，但我从未对其进行基准测试。 - MK.

这似乎是最好的方法，但@MK指出了一个严重的缺点：需要手动查找所有查询（并使其可配置）。我对Oracle没有这样的会话级属性感到非常失望。 - JayCBee

Answer 2

我怀疑没有办法做到这一点。您可以创建一个AOP建议，通过使用包装查询来修改每个executeQuery()调用。

"select * from ( " + origQuery + " ) where ROWNUM <= 5"

但我希望我们所谈论的是一个临时的或测试性质的事情。

- MK.

---

如果您动态修改所有SQL语句，则需要小心。至少有一种情况下，这种方法将不起作用。例如，select * from (select 1 a, 2 a from dual);会导致ORA-00918: column ambiguously defined错误。而更改SQL语句可能会破坏某些性能调整方法，例如存储轮廓。 - Jon Heller

@jonearles同意，看看我的评论，这只是一个临时或测试性的事情 :) - MK.