Oracle 是否通过 JDBC 支持服务器端可滚动游标？
目前正在部署基于 OFBiz 的 ERP，我们遇到了以下问题：框架的某些代码调用 resultSet.last() 来了解结果集的总行数。 使用 Oracle JDBC 驱动程序 v11 和 v10，它尝试缓存客户端内存中的所有行，从而导致 JVM 崩溃，因为它没有足够的堆空间。
经过研究，问题似乎是Oracle JDBC通过使用缓存在客户端而不是在服务器端实现了Scrollable Cursor。 使用 datadirect 驱动程序，这个问题得到了解决，但似乎对 resultset.last() 的调用需要太多时间才能完成，因此应用程序服务器中止事务
是否有任何方法可以通过 Oracle 中的 jdbc 实现可滚动游标，而无需求助于数据直接驱动程序？
知道给定结果集长度的最快方法是什么？
提前致谢 伊斯梅尔
Currently working in the deployment of an OFBiz based ERP, we've come to the following problem: some of the code of the framework calls the resultSet.last() to know the total rows of the resultset. Using the Oracle JDBC Driver v11 and v10, it tries to cache all of the rows in the client memory, crashing the JVM because it doesn't have enough heap space.
After researching, the problem seems to be that the Oracle JDBC implements the Scrollable Cursor in the client-side, instead of in the server, by the use of a cache. Using the datadirect driver, that issue is solved, but it seems that the call to resultset.last() takes too much to complete, thus the application server aborts the transaction
is there any way to implemente scrollable cursors via jdbc in oracle without resorting to the datadirect driver?
and what is the fastest way to know the length of a given resultSet??
Thanks in advance
Ismael
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
“了解给定结果集长度的最快方法是什么”
真正了解的唯一方法就是全部数一数。 您想知道电话簿中有多少个“史密斯”。 你数一下他们。
如果是一个很小的结果集，并且很快就到达了，那不是问题。 EG 电话簿中不会有很多甘道夫，无论如何你可能都想把他们全部拥有。
如果它是一个大型结果集，您也许能够进行估计，尽管这通常不是 SQL 精心设计的用途。
为了避免在客户端缓存整个结果集，您可以尝试
然后每一行都会有一个额外的列（在本例中为n），其中包含结果集中的行数。 但到达计数仍需要相同的时间，因此超时的可能性仍然很大。
一个折衷方案是获取前一百（或一千）行，并且不用担心超出此范围的分页。
"what is the fastest way to know the length of a given resultSet"
The ONLY way to really know is to count them all. You want to know how many 'SMITH's are in the phone book. You count them.
If it is a small result set, and quickly arrived at, it is not a problem. EG There won't be many Gandalfs in the phone book, and you probably want to get them all anyway.
If it is a large result set, you might be able to do an estimate, though that's not generally something that SQL is well-designed for.
To avoid caching the entire result set on the client, you can try
Then each row will have an extra column (in this case n) with the count of rows in the result set. But it will still take the same amount of time to arrive at the count, so there's still a strong chance of a timeout.
A compromise is get the first hundred (or thousand) rows, and don't worry about the pagination beyond that.
您提出的计数“解决方法”基本上使数据库服务器完成的工作增加了一倍。 它必须首先遍历所有内容以计算结果数量，然后执行相同的操作并返回结果。 加里提到的方法要好得多（count(*) over() - 分析）。 但即使在这里，也必须在第一个输出返回到客户端之前创建整个结果集。 因此，对于大输出来说，内存消耗可能会很慢。
我认为最好的方法是仅选择屏幕上您想要的页面（+1 以确定下一页是否存在），例如从 21 到 41 的行。并使用另一个按钮（用例）来在（罕见）情况下将它们全部计数需要它。
your proposed "workaround" with count basically doubles the work done by DB server. It must first walk through everything to count number of results and then do the same + return results. Much better is the method mentioned by Gary (count(*) over() - analytics). But even here the whole result set must be created before first output is returned to the client. So it is potentially slow a memory consuming for large outputs.
Best way in my opinion is select only the page you want on the screen (+1 to determine that next one exists) e.g. rows from 21 to 41. And have another button (usecase) to count them all in the (rare) case someone needs it.