• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 580
  • Last Modified:

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?
0
GouthamAnand
Asked:
GouthamAnand
  • 3
  • 3
  • 3
  • +3
1 Solution
 
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
 
sujith80Commented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
sujith80Commented:
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
 
awking00Commented:
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
 
awking00Commented:
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
 
sujith80Commented:
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
 
GouthamAnandAuthor Commented:
Thank you.
0
 
awking00Commented:
Interesting choice.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now