检查数据库表中某些记录的最快方法是什么? [英] The fastest way to check if some records in a database table?
问题描述
我有一个巨大的桌子。我想检查是否有一些记录的parent_id等于我的传递值。
目前我实现这是通过使用select count(*)from mytable where parent_id =:id;如果结果> 0,意味着它们确实存在。
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.
因为这是一个非常庞大的表,我不在乎什么是真正的记录数,我只想知道它是否存在,所以我认为count(*)是有点低效。
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.
如何以最快的方式实现这个要求?我使用Oracle 10。
How do I implement this requirement in the fastest way ? I am using Oracle 10.
根据hibernate提示&技巧 https://www.hibernate.org/118.html#A2
According to hibernate Tips & Tricks https://www.hibernate.org/118.html#A2
这表明写这样的:
整数数=(整数)session.createQuery(SELECT COUNT(*) from ....)。uniqueResult();
Integer count = (Integer) session.createQuery("select count(*) from ....").uniqueResult();
我不知道什么是uniqueResult ?它为什么做这个快
I don't know what's the magic of uniqueResult() here ? why does it make this fast ?
相较于选择1从MYTABLE其中,PARENT_ID = passingId和rowrum 2?,这是更有效的。
Compare to "select 1 from mytable where parent_id = passingId and rowrum < 2 " , which is more efficient ?
推荐答案
这是EXISTS查询是一个去,如果你在记录数量不感兴趣:
An EXISTS query is the one to go for if you're not interested in the number of records:
select 'Y' from dual where exists (select 1 from mytable where parent_id = :id)
这将返回'Y',如果记录存在,并没有其他。
This will return 'Y' if a record exists and nothing otherwise.
[在你的问题而言Hibernate的uniqueResult - 这一切都是返回一个单一的对象,当只有一个对象返回 - 而不是一个包含1个对象的集合。如果返回多个结果,该方法将抛出异常。]
[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.]
这篇关于检查数据库表中某些记录的最快方法是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!