1) Some declarations may be costly process
Defer the declarations till it is required
Search of unwanted variables and remove them
2) Prefer Built-in functions over the user defined.
3) Writing efficient conditional statements
Evaluation of expressions takes less time than execution of functions as functions involve context switch
Ex :
IF (load < 5000) AND func(id) THEN
…
END IF;
4) Check the loop statement
Always check if loop contains only those statements which depend on the loop
Exit the loop as soon as the required job is done.
5) Avoid implicit data type conversions
Ex :
DECLARE
vNum NUMBER; vChar CHAR(5);
BEGIN
vNum := vNum + 15; -- converted
vNum :=vNum + 15.0; -- not converted
vChar:=25; -- converted
vChar:=’25’; -- not converted
END
6) Choose optimized data type
Ex :
PLS_INTEGER requires less storage than INTEGER and NUMBER.
PLS_INTEGER uses machine arithmetic whereas INTEGER and NUMBER use library arithmetic which consumes more time.
7) Avoid unnecessary NOT NULL constraints.
Ex :
PROCEDURE calc_m IS
m NUMBER NOT NULL := 0; a NUMBER; b NUMBER;
BEGIN
m:=a+b;
END
PROCEDURE calc_m IS
m NUMBER; a NUMBER; b NUMBER;
BEGIN
m:=a+b;
IF m IS NULL THEN
END IF;
END