Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 545 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 440 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
efficient way to check if record exists

Hi,
I need to check if atleast one record present in table before processing rest of the statements in my PL/SQL procedure. Is there an efficient way to achieve that considering that the table is having huge number of records like 10k
I am using like below
select count(*) into flag
from T1 where ID = input_id;
if flag > 0
then perform operations
else
do nothing
Answers
-
Hi,
1. How frequent you check the condition ?
2. You can create an associate array (indexing <type > based on lookup) , you can load the collection index as lookup value (it allows whether your collection is sequential or non-sequential)
3. just look up for value (it's directly maps to location of index with in all rows )
4. You can test it, hope it works
- Pavan Kumar N
-
1. Is ID unique? There is no need to count all rows with that ID if ID is non-unique. The following will be more efficient:
select count(*)into flag
from T1
where ID = input_id
and rownum = 1;
2. What operation do you perform if flag > 0? No need for checking if at least one record present in table if performing SQL. For example, if you want to insert when no rows with such ID are in the table:
insert
into T1
select ...
from dual
where 0 = (
select count(*)
from T1
where ID = input_id
and rownum = 1
);
And use MERGE if you want to insert rows with such ID are in the or update when rows with such ID are in the table.
SY.
-
I need to check if atleast one record present in table before processing rest of the statements in my PL/SQL procedure. Is there an efficient way to achieve that considering that the table is having huge number of records like 10k
Yes - just count ONE ROW and then stop.
I am using like below select count(*) into flag from T1 where ID = input_id;
Why are you counting ALL rows? You said you just wanted ONE ROW.
1. You are standing outside a movie theater
2. Unknown to you there are 500 people in the theater
3. You are ask to go inside and determine if there are ANY (ii.e. at least one) persons inside
4. Then you are to come back outside and report if the theater is empty or not
Do you::
A. go inside and count EVERY PERSON in the theater and then report that the theater is NOT empty?
B. go inside, see ONE PERSON and then report that the theater is NOT empty?
Which answer did you choose? Please post your answer and explain why you chose that answer.
-
Hi,
I agree with your point instead of counting every records its always best to check if any one the record present in table. So can you advise on that my requirement is to check if any one record present in table T1 then proceed further with the program else don't proceed.
So as suggested by solomon, i can use the below query because ID is primary key for the table or is there any other way like using EXISTS , i mean is it possible to use exists
select count(*)
into flag
from T1
where ID = input_id
and rownum = 1;
-
Hi,
944524 wrote: ... i mean is it possible to use exists ...
Sure, you can use EXISTS if you want to:
SELECT 1 INTO flag FROM dual WHERE EXISTS ( SELECT 1 FROM t1 WHERE id = input_id ) ;
This will raise the NO_DATA_FOUND exception when input_id is not in the id column.
-
You can try to directly handle it inside SQL rather than doing it in PL/SQL
-
FOR SQL :-
yOU CAN USE NVL if you want to handle no_data_found error in count itself
if now row found with specified condition it will return 0
SELECT COUNT(NVL(COLUMN_NAME,0)) FROM TABLENAME WHERE COLUMN=VALUE;
SELECT COUNT(NVL(COLUMN_NAME,0)) FROM T1 where ID = input_id;
FOR PLSQL:-
otherwise you can go for writing exception
EXCEPTION WHEN NO_DATA_FOUND
DECLARE
L_COUNT NUMBER;
BEGIN
SELECT COUNT(*) INTO L_COUNT FROM T1 where ID = input_id;
BEGIN
EXCEPTION WHEN NO_DATA_FOUND THEN
L_COUNT:=0;
END;
END;
-
You never get NO_DATA_FOUND with COUNT. So NVL is not necessary.
Regards
Marcus
-
thank you for pointing out
SELECT nvl(max(column_name),0) FROM tablename where columnname=value;
SELECT nvl(max(column_name),0) FROM T1 where ID = input_id;
this will handle null and no data found both
-
Yes it will but at higher cost NVL + MAX instead of just COUNT and if ID isn't unique MAX will read ALL rows where ID = input_id while COUNT + ROWNUM = 1 will read just one row.
SY.