SQL Tuning Tip

While creating procedure, Function, Package or anonymous block, sometimes we need to use LOOP based on some conditions. While using that condition we can apply some tactics to make the condition better and improve the performance. Let's look at the below example:-

Say, I have to create a loop based on some condition below:-

I have to see that is there any records in the table based on condition, If yes then go to the LOOP otherwise don't do anything. Code can be below:-

DECLARE

VAR_A NUMBER;

BEGIN

SELECT COUNT(1) INTO VAR_A

FROM EMPLOYEE;

IF VAR_A >0 THEN

LOOP

----

----DO SOMETHING

----

END LOOP;

END IF;

END;

In above code there is no problem with logic as we need to perform some task if my variable is having count more than 0. But it can be improved by using ROWNUM column in where condition as shown below:-

DECLARE

VAR_A NUMBER;

BEGIN

SELECT COUNT(1) INTO VAR_A
FROM EMPLOYEE
WHERE ROWNUM<=2;

IF VAR_A >0 THEN

LOOP
----
----DO SOMETHING
----

END LOOP;

END IF;

END;

Above code will also do the same thing what the earlier code could have done but while counting it can take little time than first code.