|
|
Deterministic Functions
in PL/SQL
Oracle Tips by Burleson Consulting
|
The following Tip is from the
outstanding book "Oracle
PL/SQL Tuning: Expert Secrets for High Performance Programming" by
Dr. Tim Hall, Oracle ACE of the year, 2006:
A function is considered deterministic if it
always returns the same result for a specific input value. The
Oracle documentation claims that defining pipelined table functions as
deterministic by using the DETERMINISTIC clause allows Oracle to
buffer their rows, thereby preventing multiple executions. But I can
find no evidence to support this claim.
The test_deterministic.sql script defines a
package containing two pipelined table functions, one of which is
defined as deterministic. It then executes the functions
multiple times using two different methods to compare their
performance.
test_deterministic.sql
CREATE OR
REPLACE PACKAGE deterministic_api AS
TYPE
t_out_row IS RECORD (
id NUMBER
);
TYPE
t_out_tab IS TABLE OF t_out_row;
FUNCTION no_deterministic
RETURN t_out_tab PIPELINED;
FUNCTION deterministic
RETURN t_out_tab PIPELINED DETERMINISTIC;
END
deterministic_api;
/
SHOW ERRORS
CREATE OR
REPLACE PACKAGE BODY deterministic_api AS
FUNCTION no_deterministic
RETURN t_out_tab PIPELINED
IS
l_row t_out_row;
BEGIN
FOR i IN 1 .. 100 LOOP
l_row.id := i;
PIPE ROW (l_row);
END LOOP;
RETURN;
END no_deterministic;
FUNCTION deterministic
RETURN t_out_tab PIPELINED DETERMINISTIC
IS
l_row t_out_row;
BEGIN
FOR i IN 1 .. 100 LOOP
l_row.id := i;
PIPE ROW (l_row);
END LOOP;
RETURN;
END deterministic;
END
deterministic_api;
/
SHOW ERRORS
PROMPT
PROMPT Test multiple separate calls.
PROMPT =============================
SET SERVEROUTPUT ON
DECLARE
l_start NUMBER;
l_count NUMBER;
BEGIN
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. 5000 LOOP
SELECT COUNT(*)
INTO l_count
FROM TABLE(deterministic_api.deterministic);
END LOOP;
DBMS_OUTPUT.put_line('Deterministic : ' || (DBMS_UTILITY.get_time
- l_start));
l_start := DBMS_UTILITY.get_time;
FOR i
IN 1 .. 5000 LOOP
SELECT COUNT(*)
INTO l_count
FROM TABLE(deterministic_api.no_deterministic);
END LOOP;
DBMS_OUTPUT.put_line('No Deterministic: ' || (DBMS_UTILITY.get_time -
l_start));
END;
/
PROMPT Test
multiple calls in a single statement.
PROMPT ==========================================
SET TIMING ON
PROMPT
PROMPT Deterministic
PROMPT =============
SELECT COUNT(*)
FROM TABLE(deterministic_api.deterministic) a,
TABLE(deterministic_api.deterministic) b,
TABLE(deterministic_api.deterministic) c,
TABLE(deterministic_api.deterministic) d,
TABLE(deterministic_api.deterministic) e
WHERE a.id = b.id
AND b.id = c.id
AND c.id = d.id
AND d.id = e.id;
PROMPT
PROMPT No Deterministic
PROMPT ================
SELECT COUNT(*)
FROM TABLE(deterministic_api.no_deterministic) a,
TABLE(deterministic_api.no_deterministic)
b,
TABLE(deterministic_api.no_deterministic)
c,
TABLE(deterministic_api.no_deterministic)
d,
TABLE(deterministic_api.no_deterministic) e
WHERE a.id = b.id
AND b.id = c.id
AND c.id = d.id
AND d.id = e.id;
SET TIMING
OFF
DROP PACKAGE
deterministic_api;
The output from this script is displayed
below.
SQL> @test_deterministic.sql
Package
created.
No errors.
Package body
created.
No errors.
Test
multiple separate calls.
=============================
Deterministic : 870
No
Deterministic: 805
PL/SQL
procedure successfully completed.
Test
multiple calls in a single statement.
==========================================
Deterministic
=============
COUNT(*)
----------
100
1 row
selected.
Elapsed:
00:00:00.21
No
Deterministic
================
COUNT(*)
----------
100
1 row
selected.
Elapsed:
00:00:00.18
Package
dropped.
Multiple runs of this test show that the
deterministic function is sometimes slower and sometimes faster than
the non-deterministic function, but there does not seem to be any
significant difference. The results from this test indicate that
the DETERMINISTIC clause, although syntactically correct, has no
impact on pipelined table function performance.
This result is not really surprising. If
Oracle did attempt to buffer rows from a deterministic table function
that returned many thousands of rows, it could result in performance
issues due to excessive memory consumption.
Miscellaneous Information
For the sake of completeness, it is worth
mentioning a few small points about table functions.
-
Table functions can only contain DML
statements if they are defined with the AUTONOMOUS_TRANSACTION
pragma, or the DML is itself wrapped in a procedure call that is an
autonomous transaction.
-
DML statements cannot be executed against
table functions, but if the table function is incorporated into a
view, the view can have INSTEAD OF triggers defined against it.
-
Exception handling is the same for table
functions as it is for other PL/SQL functions, such that any
unhandled exceptions are propagated back to the calling PL/SQL or
SQL.
|