Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

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

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 ?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...