Databases Reference
In-Depth Information
There's more...
The performance gain obtained in the example is huge, as we are testing against a condition
that is true for all records in the table. The quantity sold is always greater than 1; this can be
verified with a simple command:
SELECT COUNT(*) FROM SALES WHERE AMOUNT_SOLD > 1
In this situation, it's better to first test the condition which is true less often. If this
condition is false (and the conditions are related with a logical AND to each other), the
whole predicate (compound by adding more conditions) is false, hence, the remaining
conditions are not checked.
This behavior is called short-circuit IF, because the execution flow takes the shortest route
to the destination. A similar behavior also occurs in logical ORed conditions, but in this case,
the short circuit shows when the first condition is true and hence the predicate.
Avoiding recursion
In this recipe, we will investigate the use of recursive PL/SQL functions and their impact
on performance.
How to do it...
The following steps demonstrate recursive functions:
1.
Connect to the
SH
schema:
CONNECT sh@TESTDB/sh
2.
Create the
FACTORIAL_RECURSIVE
function to calculate the factorial of a given
number (which is the product of all positive integers less than or equal to the given
number) using the well-known recursive algorithm, as follows:
CREATE OR REPLACE FUNCTION FACTORIAL_RECURSIVE (ANUM NUMBER)
RETURN NUMBER IS
AVALUE NUMBER;
BEGIN
IF ANUM <= 1 THEN
AVALUE := 1;
ELSE
AVALUE := ANUM * FACTORIAL_RECURSIVE(ANUM - 1);
END IF;
RETURN AVALUE;
END;
