database 检查数据库表中是否有某些记录的最快方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2131168/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
The fastest way to check if some records in a database table?
提问by Sawyer
I have a huge table to work with . I want to check if there are some records whose parent_id equals my passing value . currently what I implement this is by using "select count(*) from mytable where parent_id = :id"; if the result > 0 , means the they do exist.
我有一张大桌子要处理。我想检查是否有一些记录的 parent_id 等于我的传递值。目前我是通过使用“select count(*) from mytable where parent_id = :id”来实现的；如果结果 > 0 ，则表示它们确实存在。
Because this is a very huge table , and I don't care what's the exactly number of records that exists , I just want to know whether it exists , so I think count(*) is a bit inefficient.
因为这是一个非常大的表，我不在乎存在的记录的确切数量是多少，我只想知道它是否存在，所以我认为 count(*) 有点低效。
How do I implement this requirement in the fastest way ? I am using Oracle 10.
如何以最快的方式实现此要求？我正在使用 Oracle 10。
#According to hibernate Tips & Tricks https://www.hibernate.org/118.html#A2
根据 hibernate Tips & Tricks https://www.hibernate.org/118.html#A2
It suggests to write like this :
它建议这样写：
Integer count = (Integer) session.createQuery("select count(*) from ....").uniqueResult();
Integer count = (Integer) session.createQuery("select count(*) from ....").uniqueResult();
I don't know what's the magic of uniqueResult() here ? why does it make this fast ?
我不知道这里的 uniqueResult() 有什么魔力？为什么它这么快？
Compare to "select 1 from mytable where parent_id = passingId and rowrum < 2 " , which is more efficient ?
与“select 1 from mytable where parent_id=passingId and rowrum < 2”相比，哪个更有效？
回答by Nick Pierpoint
An EXISTS query is the one to go for if you're not interested in the number of records:
如果您对记录数不感兴趣，可以使用 EXISTS 查询：
select 'Y' from dual where exists (select 1 from mytable where parent_id = :id)
This will return 'Y' if a record exists and nothing otherwise.
如果记录存在，则返回“Y”，否则返回。
[In terms of your question on Hibernate's "uniqueResult" - all this does is return a single object when there is only one object to return - instead of a set containing 1 object. If multiple results are returned the method throws an exception.]
[就您对 Hibernate 的“uniqueResult”的问题而言 - 所有这一切都是在只有一个对象要返回时返回一个对象 - 而不是包含 1 个对象的集合。如果返回多个结果，该方法将引发异常。]
回答by meriton
select count(*) should be lighteningly fast if you have an index, and if you don't, allowing the database to abort after the first match won't help much.
如果您有索引，则 select count(*) 应该快得惊人，如果没有，则允许数据库在第一次匹配后中止将无济于事。
But since you asked:
但既然你问：
boolean exists = session.createQuery("select parent_id from Entity where parent_id=?")
.setParameter(...)
.setMaxResults(1)
.uniqueResult()
!= null;
(Some syntax errors to be expected, since I don't have a hibernate to test against on this computer)
（预期会出现一些语法错误，因为我没有在这台计算机上测试的休眠状态）
For Oracle, maxResults is translated into rownum by hibernate.
对于 Oracle，maxResults 被 hibernate 转换成 rownum。
As for what uniqueResult() does, read its JavaDoc! Using uniqueResult instead of list() has no performance impact; if I recall correctly, the implementation of uniqueResult delegates to list().
至于 uniqueResult() 的作用，请阅读它的 JavaDoc！使用 uniqueResult 而不是 list() 对性能没有影响；如果我没记错的话，uniqueResult 的实现委托给 list()。
回答by Adam Musch
There's no real difference between:
之间没有真正的区别：
select 'y'
from dual
where exists (select 1
from child_table
where parent_key = :somevalue)
and
和
select 'y'
from mytable
where parent_key = :somevalue
and rownum = 1;
... at least in Oracle10gR2 and up. Oracle's smart enough in that release to do a FAST DUAL operation where it zeroes out any real activity against it. The second query would be easier to port if that's ever a consideration.
...至少在 Oracle10gR2 及更高版本中。Oracle 在该版本中足够聪明，可以执行 FAST DUAL 操作，将针对它的任何实际活动归零。如果考虑到这一点，则第二个查询将更容易移植。
The real performance differentiator is whether or not the parent_key column is indexed. If it's not, then you should run something like:
真正的性能差异在于 parent_key 列是否被索引。如果不是，那么你应该运行如下：
select 'y'
from dual
where exists (select 1
from parent_able
where parent_key = :somevalue)
回答by Thilo
First of all, you need an index on mytable.parent_id.
首先，你需要一个关于 mytable.parent_id 的索引。
That should make your query fast enough, even for big tables (unless there are also a lot of rows with the same parent_id).
这应该使您的查询足够快，即使对于大表也是如此（除非还有很多具有相同 parent_id 的行）。
If not, you could write
如果没有，你可以写
select 1 from mytable where parent_id = :id and rownum < 2
which would return a single row containing 1, or no row at all. It does not need to count the rows, just find one and then quit. But this is Oracle-specific SQL (because of rownum), and you should rather not.
这将返回包含 1 的单行，或者根本没有行。它不需要计算行数，只需找到一个然后退出。但这是特定于 Oracle 的 SQL（因为有 rownum），您不应该这样做。
回答by bertolami
For DB2 there is something like select * from mytable where parent_id = ? fetch first 1 row only. I assume that something similar exists for oracle.
对于 DB2，有类似select * from mytable where parent_id = ? fetch first 1 row only. 我认为 oracle 存在类似的东西。
回答by MinhD
This query will return 1 if any record exists and 0 otherwise:
如果任何记录存在，此查询将返回 1，否则返回 0：
SELECT COUNT(1) FROM (SELECT 1 FROM mytable WHERE ROWNUM < 2);
It could help when you need to check table data statistics, regardless table size and any performance issue.
当您需要检查表数据统计信息时，无论表大小和任何性能问题，它都会有所帮助。
