oracle - The fastest way to check if some records in a database table?

Question

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.

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.

How do I implement this requirement in the fastest way ? I am using Oracle 10.

#

According to 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();

I don't know what's the magic of uniqueResult() here ? why does it make this fast ?

Compare to "select 1 from mytable where parent_id = passingId and rowrum < 2 " , which is more efficient ?

Answer 1

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)

This will return 'Y' if a record exists and nothing otherwise.

[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.]