How to find a record exists or not in a table in Oracle?

I want to findout whether a record for the given criteria exists or not in a table.
I have written the query like this
Select count(P_InvoiceID) into invoiceCount
  from voucher
  where voucher.id = P_InvoiceID and voucher.scac = P_SCACCode;

But I do not require the count, just I want exist or not exist (boolean), Is there any other way of finding it out?
GouthamAnandAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaitu chaituCommented:
Select case when count(P_InvoiceID) >= 1 then 'true' else 'false' end result
  from voucher
  where voucher.id = P_InvoiceID and voucher.scac = P_SCACCode;
0
SujithData ArchitectCommented:
The following query will return a 1 if there are any records matching your condition.
It will not return any records if there are no matching records. To further speed up you may use FIRST_ROWS hint to the query.
Select 1
from voucher 
where voucher.id = P_InvoiceID and voucher.scac = P_SCACCode
and rownum = 1

Open in new window

0
GouthamAnandAuthor Commented:
How to use FIRST_ROWS hint to the query, can you give me an example? becuase I require the result to be very fast.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SujithData ArchitectCommented:
Select /*+ FIRST_ROWS(1) */ 1
from voucher
where voucher.id = P_InvoiceID and voucher.scac = P_SCACCode
and rownum = 1
0
GouthamAnandAuthor Commented:
I have a question on this, why do we require /*+ FIRST_ROWS(1) */ 1 and
rownum = 1.

why do we require these both?

Just can you explain me what these both are doing individually?
0
awking00Information Technology SpecialistCommented:
From Oracle documentation -
The FIRST_ROWS(n) hint instructs Oracle to optimize an individual SQL statement for fast response, choosing the plan that returns the first n rows most efficiently.

The rownum = 1 will return only one record. In your case, if the where condition existed for four rows, the results would be -
1
1
1
1
0
johnsoneSenior Oracle DBACommented:
Another way would be this
Select 1 from dual
where exists (select 1
from voucher
where voucher.id = P_InvoiceID and voucher.scac = P_SCACCode);

Open in new window

0
sdstuberCommented:
to help with speed,  create an index on id and scac (a single index with both columns, not two indexes of one column each)  this assumes id and scac will be fairly selective.

0
awking00Information Technology SpecialistCommented:
Just noticed I should have said,
"The rownum = 1 will return only one record. In your case, if the where condition existed for four rows, WITHOUT IT, the results would be"
1
1
1
1
0
SujithData ArchitectCommented:
FIRST_ROWS - is a hint to the optimizer for faster result, it doesnt affect the actual results of the query.
rownum = 1, is to limit the output from the query to only one record. I.e. if you want to see whether a matching record is there in the table you only have to recieve one matching record right?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GouthamAnandAuthor Commented:
Thank you.
0
awking00Information Technology SpecialistCommented:
Interesting choice.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.