SQL & PL/SQL

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

WHERE COLUMN=VALUE;

where ID = input_id;

FOR PLSQL:-

otherwise you can go for writing  exception

EXCEPTION WHEN NO_DATA_FOUND

DECLARE

L_COUNT NUMBER;

BEGIN

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;

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.

is there any way to handle no_data_found in sql not plsql besides max

SELECT nvl(max(column_name),0)  FROM  tablename  where columnname=value;

ANY aggregate function with IMPLICIT group by will not raise NO_DATA_FOUND, so

SELECT  COUNT(*)

  FROM  table_name

  WHERE column_name = value

    AND rownum = 1;

will not raise NO_DATA_FOUND.

SY.

YES thats true that will return 0.

944524 wrote:

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

The approach is not thread safe in normal programming speak - or multi-processing/multi-session/multi-transaction safe in database speak.

At T1 process 1 deletes rows foo.

At T2 your process does a select count and find that row foo exists.

At T3 process 1 commits and row foo has gone to the big bit bucket in the sky.

At T4 you process evaluates "flag > 0" predicate, finds it true, incorrectly assumes that row foo exists, and proceeds to process based on that erroneous assumption.

The correct method is to force some kind of serialisation on row foo to ensure it exists when decisions are made to process business data, based on the fact that foo is actually there.

This can be done by locking row foo (even if it is not updated), to ensure that the processing done based on foo, is done while foo actually does exist. So instead of a select count to test whether foo exists at that point in time, a select for update to lock foo, and ensure it does exist when "perform operations" happen.

This is a critical concept to understand. I have seen (and still see) lots of code that ignores this. Yes, such code may well work fine 99% of the time. But when it does not, and processing occurs based on an erroneous assumption, data integrity is lost. And this can have a severe impact on the business, with dire financial implications, customers loosing their trust in the business, failed auditing, with lots more of other bad things.

can you clarify with an example to handle this i didn't get foo???

foo = any arbitrary row that the processes are looking for

Hi,

I would go with Frank Kulash's existence check method with a slight modification to handle nulls. This query will check only for the existence of the record and will not count the total records : -

SELECT  count(1)

INTO    flag

FROM    dual

WHERE   EXISTS (

                   SELECT  1

                   FROM    t1

                   WHERE   id  = input_id

               )

;

just another way:

for i in (select 1 from dual where dummy ='X' having count(*) > 0) loop

  --this block runs if the query returns a row, which means there are min. one row in the main query

     null;

end loop;

Bawer wrote:

just another way:

for i in (select 1 from dual where dummy ='X' having count(*) > 0) loop
  --this block runs if the query returns a row, which means there are min. one row in the main query
     null;
end loop;

But you get exactly the concurrency problems described by Billy

you were right, if I want to check by more processes.

If there is only one process which asks the table, no locking is needed. how efficient is it if you locks a table/row in a loop (assuming even there is only one loop)?

it all depends on the business logic. What I wrote is only a part.

can you let me know the query how i can use in foo not able to get

English follow

Instead of a "select count(*)" SQL and then an "update" SQL, you need to do a "select for update" SQL (prevents it from being deleted/changed), and then do the "update" SQL.

Or simply do the "update" SQL directly - without checking if the row exists up front. After the update, check if a row was updated (using SQL%RowCount variable).

If so, then success.. Commit.

If not, then failure. Rollback and raise an application exception.

I don't want to perform the update or insert or proceed further if there is no record exist in the table

Hi,

944524 wrote:

I don't want to perform the update or insert or proceed further if there is no record exist in the table

Then Billy's suggestion (reply #23) is perfect for you.  Simply do an UPDATE statement.  If no such row exists, then nothing will be changed.