我有一个巨大的桌子。我想检查是否有一些记录的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提示&技巧 www.hibernate/118.html#A2
According to hibernate Tips & Tricks www.hibernate/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.]
更多推荐
检查数据库表中某些记录的最快方法是什么?
发布评论