I've got a huge table to utilize . I wish to see if you will find some records whose parent_id equals my passing value . presently things i implement this is to apply "choose count(*) from mytable where parent_id = :id" when the result > , means the they are doing exist.

Since this is a really huge table , and that i don't care what is the exactly quantity of records that is available , Among the finest to understand whether or not this is available , and so i think count(*) is a little inefficient.

How do you implement this requirement within the quickest way ? I'm using Oracle 10.

#

Based on hibernate Tips &lifier Methods https://www.hibernate.org/118.html#A2

It indicates to create such as this :

Integer count = (Integer) session.createQuery("choose count(*) from ....").uniqueResult()

I'm not sure what is the miracle of uniqueResult() here ? how come it get this to fast ?

Rival "choose 1 from mytable where parent_id = passingId and rowrum < 2 inch , that is more effective ?

An Is available totally the main one to choose if you are not thinking about the amount of records:

select 'Y' from dual where exists (select 1 from mytable where parent_id = :id)

This can return 'Y' if your record is available and absolutely nothing otherwise.

[When it comes to your question on Hibernate's "uniqueResult" - all of this does is return just one object when there's just one resist return - rather than a collection that contains 1 object. If multiple answers are came back the technique throws the best.]

choose count(*) ought to be lighteningly fast for those who have a catalog, and when you do not, permitting the database to abort following the first match will not help much.

Consider you requested:

boolean exists = session.createQuery("select parent_id from Entity where parent_id=?")
                        .setParameter(...)
                        .setMaxResults(1)
                        .uniqueResult() 
                 != null;

(Some syntax errors to become expected, since i have do not have a hibernate to check against about this computer)

For Oracle, maxResults is converted into rownum by hibernate.

For what uniqueResult() does, read its JavaDoc! Using uniqueResult rather than list() doesn't have performance impact basically recall properly, the implementation of uniqueResult associates to list out().

To begin with, you'll need a catalog on mytable.parent_id.

Which should build your query fast enough, for large tables (unless of course you will find also lots of rows with similar parent_id).

Otherwise, you can write

select 1 from mytable where parent_id = :id and rownum < 2

which may return just one row that contains 1, or no row whatsoever. It need not count the rows, just locate one after which quit. But this really is Oracle-specific SQL (due to rownum), and you ought to rather not.

There is no real distinction 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;

... a minimum of in Oracle10gR2 or more. Oracle's wise enough for the reason that release to perform a FAST DUAL operation where it zeroes out any real activity against it. The 2nd query could be simpler to port if that is ever considered.

The actual performance differentiator is if parents_key column is indexed. When not, then you definitely should run something similar to:

select 'y' 
  from dual 
 where exists (select 1 
                 from parent_able 
                where parent_key = :somevalue)

For DB2 there's something similar to select * from mytable where parent_id = ? fetch first 1 row only. I suppose that such like is available for oracle.