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.
目前正在部署基于OFBiz的ERP,我们遇到了以下问题:框架的一些代码调用resultSet.last()来了解结果集的总行数。使用Oracle JDBC驱动程序v11和v10,它会尝试缓存客户端内存中的所有行,从而导致JVM崩溃,因为它没有足够的堆空间。
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
在研究之后,问题似乎是Oracle JDBC通过使用缓存在客户端而不是在服务器中实现Scrollable Cursor。使用datadirect驱动程序,该问题已解决,但似乎对resultset.last()的调用需要太多才能完成,因此应用程序服务器会中止事务
is there any way to implemente scrollable cursors via jdbc in oracle without resorting to the datadirect driver?
有什么方法可以通过oracle中的jdbc实现可滚动游标而无需求助于datadirect驱动程序?
and what is the fastest way to know the length of a given resultSet??
知道给定resultSet长度的最快方法是什么?
Thanks in advance Ismael
在此先感谢Ismael
2 个解决方案
#1
"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.
“了解给定resultSet长度的最快方法是什么?”真正知道的唯一方法就是统计它们。你想知道电话簿中有多少'SMITH'。你算一算吧。如果它是一个小的结果集,并迅速到达,这不是一个问题。 EG电话簿里不会有很多Gandalfs,你可能想要把它们全部搞定。
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.
如果它是一个大的结果集,你可能能够做一个估计,虽然这通常不是SQL设计得很好的东西。
To avoid caching the entire result set on the client, you can try
为避免在客户端上缓存整个结果集,您可以尝试
select id, count(1) over () n from junk;
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.
然后每行将有一个额外的列(在本例中为n),其中包含结果集中的行数。但它仍然需要相同的时间才能到达计数,所以仍有很大的机会暂停。
A compromise is get the first hundred (or thousand) rows, and don't worry about the pagination beyond that.
妥协是获得前100行(或数千行),并且不要担心超出该行的分页。
#2
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.
你提出的计划“解决方法”基本上是DB服务器完成的工作的两倍。它必须首先遍历所有内容以计算结果数量,然后执行相同的+返回结果。更好的是Gary提到的方法(count(*)over() - analytics)。但即使在这里,也必须在将第一个输出返回给客户端之前创建整个结果集。因此,大输出的内存消耗可能会很慢。
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.
在我看来,最好的方法是在屏幕上仅选择您想要的页面(+1以确定下一个存在的页面),例如从21到41的行。并且有另一个按钮(用例)在所有人需要它的(罕见)情况下对它们进行计数。
#1
"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.
“了解给定resultSet长度的最快方法是什么?”真正知道的唯一方法就是统计它们。你想知道电话簿中有多少'SMITH'。你算一算吧。如果它是一个小的结果集,并迅速到达,这不是一个问题。 EG电话簿里不会有很多Gandalfs,你可能想要把它们全部搞定。
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.
如果它是一个大的结果集,你可能能够做一个估计,虽然这通常不是SQL设计得很好的东西。
To avoid caching the entire result set on the client, you can try
为避免在客户端上缓存整个结果集,您可以尝试
select id, count(1) over () n from junk;
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.
然后每行将有一个额外的列(在本例中为n),其中包含结果集中的行数。但它仍然需要相同的时间才能到达计数,所以仍有很大的机会暂停。
A compromise is get the first hundred (or thousand) rows, and don't worry about the pagination beyond that.
妥协是获得前100行(或数千行),并且不要担心超出该行的分页。
#2
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.
你提出的计划“解决方法”基本上是DB服务器完成的工作的两倍。它必须首先遍历所有内容以计算结果数量,然后执行相同的+返回结果。更好的是Gary提到的方法(count(*)over() - analytics)。但即使在这里,也必须在将第一个输出返回给客户端之前创建整个结果集。因此,大输出的内存消耗可能会很慢。
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.
在我看来,最好的方法是在屏幕上仅选择您想要的页面(+1以确定下一个存在的页面),例如从21到41的行。并且有另一个按钮(用例)在所有人需要它的(罕见)情况下对它们进行计数。