Table of Contents
DETERMINISTIC & PIPELINED TABLE Functions WITH RESULT CACHE
DETERMINISTIC CLAUSE:
A deterministic function must return the same value on two distinct invocations. Condition is if the parameters provided in two invocation are same.
The deterministic clause may appear at most once in a function declaration or definition. A function so marked is called deterministic function.
The deterministic clause is an assertion that the function must obey the semantic rules. If the function does not then neither the compiler, SQL execution or PLSQL execution may diagnose the problem and wrong result may be silently produced.
You must specify the deterministic keyword if you intend to invoke the function:
- in the expression of a function based index.
- in a virtual column definition
- from the query of a materialized view that is marked REFRESH FAST or ENABLE QUERY REWRITE
Usage:
- When the database encounters a deterministic function, it tries to use previously calculated results. Rather than re executing the function.
- If you change the function then you must manually rebuild all the dependent function-based indexes and materialized views.
- Do not specify deterministic to a function whose results depends on the state of a session variables or schema objects. The reason is as result might vary across invocation.
- Don't specify this clause to define a function that uses package variable.
- Avoid this clause to define a function that uses the database in any way that might affect the return result.
- When the DETERMINISTIC option appears the compiler may use the mark to improve the performance of execution of the function.
- Its a good practice to make functions that fall into these categories DETERMINISTIC:
- Function used in WHERE, GROUP BY or ORDER BY clause
- Function that map or order method of a SQL type
- That help determine whether or where a row appear in the result set.
TABLE FUNCTIONS:
- Table functions are functions that act like table in a select statement, i.e function that returns collection of rows.
- They can be queried like a regular table using TABLE operator in the FROM clause. You can query the columns and rows from the array returned by the function.
- Table functions are a great way to programmatically construct data sets. In addition, they play a key role in data warehouse. There it needs to perform multiple transformation of data within a single sql statement.
- Regular table functions require collections to be fully populated before they are returned. Since collections are held in memory, this can be a problem to hold large collection in memory. This is because they will occupy more memory and will take long time to return the first row. Therefore normal table functions are not recommended for Extraction Transformation and Load (ETL) operation.
- Performance of table function can be improved by
- pipelining and
- parallelization
PIPELINED TABLE FUNCTION:
- Pipelined table functions negates the need to build huge collections. They are doing this by piping rows out of the function as they are created. In this way lot of memories can be saved. In addition, it allows the further processing to start before all the rows are generated.
- Pipelined table function includes PIPELINED clause and the PIPE ROW call to push rows out of the function as soon as they are created. So they don't have to wait to building up a table collection.
- There is an empty RETURN call, as there is no collection to return from the function.
PARALLEL ENABLED TABLE FUNCTION:
- Parallel enabling table functions allows their workload to be split between multiple slave processes. It may result in faster execution.
- For a table function to be executed in parallel it must have partitioned input parameter.
- Parallelism is turned on for a table function if and only if both of the following conditions are met:
- The function has a PARALLEL_ENABLE clause in its declaration.
- Exactly one REF CURSOR is specified with a PARTITION BY clause.
If the PARTITION BY clause is not specified for any input REF CURSOR as a part of PARALLEL_ENABLE clause, the SQL compiler cannot determine how to partition data correctly.
Example of a Normal Table Function:
1st Example:
--DETERMINISTIC & PIPELINED TABLE Functions WITH RESULT CACHE: Example: Normal Table Function
create or replace package emp_pkg
is
type rc1 is ref cursor return employees%rowtype;
end;
/
create table employees_dml
as
select * from employees
where 1 = 2;
create or replace type emp_ot is object
(
EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25),
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE,
JOB_ID VARCHAR2(10),
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4)
);
/
create or replace type emp_nt is table of emp_ot;
/
create or replace function doubled(row_in emp_pkg.rc1)
return emp_nt
is
type emp_t is table of employees%rowtype index by pls_integer;
t_emp emp_t;
emp_double emp_nt := emp_nt();
begin
loop
fetch row_in bulk collect into t_emp limit 100;
exit when t_emp.count = 0;
for i in 1..t_emp.count
loop
emp_double.extend;
emp_double(emp_double.last) := emp_ot(t_emp(i).employee_id,
t_emp(i).FIRST_NAME,
t_emp(i).LAST_NAME,
t_emp(i).EMAIL,
t_emp(i).PHONE_NUMBER,
t_emp(i).HIRE_DATE,
t_emp(i).JOB_ID,
t_emp(i).SALARY,
t_emp(i).COMMISSION_PCT,
t_emp(i).MANAGER_ID,
t_emp(i).DEPARTMENT_ID
);
end loop;
end loop;
close row_in;
return emp_double;
end;
/
2nd Example:
--DETERMINISTIC & PIPELINED TABLE Functions WITH RESULT CACHE: Example: Pipelined Table Functions
create or replace function doubled_pl(row_in emp_pkg.rc1)
return emp_nt
pipelined
is
type emp_t is table of employees%rowtype index by pls_integer;
t_emp emp_t;
begin
loop
fetch row_in bulk collect into t_emp limit 100;
exit when t_emp.count = 0;
for i in 1..t_emp.count
loop
pipe row(emp_ot(t_emp(i).employee_id,
t_emp(i).FIRST_NAME,
t_emp(i).LAST_NAME,
t_emp(i).EMAIL,
t_emp(i).PHONE_NUMBER,
t_emp(i).HIRE_DATE,
t_emp(i).JOB_ID,
t_emp(i).SALARY,
t_emp(i).COMMISSION_PCT,
t_emp(i).MANAGER_ID,
t_emp(i).DEPARTMENT_ID
));
end loop;
end loop;
return;
end;
/
Execution:
----DETERMINISTIC & PIPELINED TABLE Functions WITH RESULT CACHE: Example: Function Execution
set timing on;
insert into employees_dml
select * from table(doubled(cursor(select * from employees)))
/
Output:
107 rows inserted.
Elapsed: 00:00:00.204
truncate table employees_dml;
insert into employees_dml
select * from table(doubled_pl(cursor(select * from employees)))
/
Output:
Table EMPLOYEES_DML truncated.
Elapsed: 00:00:00.422
107 rows inserted.
Elapsed: 00:00:00.000
Performance wise Pipelined Table function took less time to insert the same no of records.
Parallel Enable Pipelined Table Function Example:
--DETERMINISTIC & PIPELINED TABLE Functions WITH RESULT CACHE: Example: Parallel enabled Pipelined Table Function
create or replace package pipe_pkg
is
type emp_ot is record
(
EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25),
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE,
JOB_ID VARCHAR2(10),
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4)
);
type emp_nt is table of emp_ot;
function doubled_pl(row_in emp_pkg.rc1)
return emp_nt
pipelined
parallel_enable(partition row_in by hash (employee_id));
end pipe_pkg;
/
create or replace package body pipe_pkg
is
function doubled_pl(row_in emp_pkg.rc1)
return emp_nt
pipelined
parallel_enable(partition row_in by hash(employee_id))
is
type emp_t is table of employees%rowtype index by pls_integer;
t_emp emp_t;
begin
loop
fetch row_in bulk collect into t_emp limit 100;
exit when t_emp.count = 0;
for i in 1..t_emp.count
loop
pipe row(t_emp(i));
end loop;
end loop;
return;
end doubled_pl;
end pipe_pkg;
/
select * from table(pipe_pkg.doubled_pl(cursor(select * from employees)));
NO_DATA_NEEDED EXCEPTION:
A pipelined table function may create more data than is needed by the process querying it. If this happened then pipelined table function execution stops and raises the NO_DATA_NEEDED exception. This doesn't need to be explicitly handled except you have included an OTHERS exception handler.
In the above example the below query returns 107 rows.
select * from table(doubled_pl(cursor(select * from employees)));
But when we execute the below query it will not show any error:
select * from table(doubled_pl(cursor(select * from employees)))
where rownum <= 5;
If you include an other exception handler, then it will capture NO_DATA_NEEDED exception. In addition, it potentially run some error handler code, it shouldn't.
--DETERMINISTIC & PIPELINED TABLE Functions WITH RESULT CACHE: Example: NO_DATA_NEEDED Exception
create or replace function doubled_pl(row_in emp_pkg.rc1)
return emp_nt
pipelined
is
type emp_t is table of employees%rowtype index by pls_integer;
t_emp emp_t;
begin
loop
fetch row_in bulk collect into t_emp limit 100;
exit when t_emp.count = 0;
for i in 1..t_emp.count
loop
pipe row(emp_ot(t_emp(i).employee_id,
t_emp(i).FIRST_NAME,
t_emp(i).LAST_NAME,
t_emp(i).EMAIL,
t_emp(i).PHONE_NUMBER,
t_emp(i).HIRE_DATE,
t_emp(i).JOB_ID,
t_emp(i).SALARY,
t_emp(i).COMMISSION_PCT,
t_emp(i).MANAGER_ID,
t_emp(i).DEPARTMENT_ID
));
end loop;
end loop;
return;
exception
when others then
dbms_output.put_line('Others Handler');
raise;
end;
/
select * from table(doubled_pl(cursor(select * from employees)))
where rownum <= 5;
If you plan to use OTHERS exception handler, then there must be a trap for NO_DATA_NEEDED exception.
--DETERMINISTIC & PIPELINED TABLE Functions WITH RESULT CACHE: Example: NO_DATA_NEEDED Exception
create or replace function doubled_pl(row_in emp_pkg.rc1)
return emp_nt
pipelined
is
type emp_t is table of employees%rowtype index by pls_integer;
t_emp emp_t;
begin
loop
fetch row_in bulk collect into t_emp limit 100;
exit when t_emp.count = 0;
for i in 1..t_emp.count
loop
pipe row(emp_ot(t_emp(i).employee_id,
t_emp(i).FIRST_NAME,
t_emp(i).LAST_NAME,
t_emp(i).EMAIL,
t_emp(i).PHONE_NUMBER,
t_emp(i).HIRE_DATE,
t_emp(i).JOB_ID,
t_emp(i).SALARY,
t_emp(i).COMMISSION_PCT,
t_emp(i).MANAGER_ID,
t_emp(i).DEPARTMENT_ID
));
end loop;
end loop;
return;
exception
when no_data_needed then
raise;
when others then
dbms_output.put_line('Others Handler');
raise;
end;
/
select * from table(doubled_pl(cursor(select * from employees)))
where rownum <= 5;
--DETERMINISTIC & PIPELINED TABLE Functions WITH RESULT CACHE: Example: NO_DATA_NEEDED Exception
create or replace function doubled_pl(row_in emp_pkg.rc1, ex varchar2)
return emp_nt
pipelined
is
type emp_t is table of employees%rowtype index by pls_integer;
t_emp emp_t;
begin
loop
fetch row_in bulk collect into t_emp limit 50;
exit when t_emp.count = 0;
for i in 1..t_emp.count
loop
begin
pipe row(emp_ot(t_emp(i).employee_id,
t_emp(i).FIRST_NAME,
t_emp(i).LAST_NAME,
t_emp(i).EMAIL,
t_emp(i).PHONE_NUMBER,
t_emp(i).HIRE_DATE,
t_emp(i).JOB_ID,
t_emp(i).SALARY,
t_emp(i).COMMISSION_PCT,
t_emp(i).MANAGER_ID,
t_emp(i).DEPARTMENT_ID
));
end;
end loop;
if t_emp.count >= 50 then
case ex when 'no_data_needed' then
raise no_data_needed;
end case;
end if;
end loop;
return;
end;
/
insert into employees_dml
select * from table(doubled_pl(cursor(select * from employees), 'no_data_needed'))
/
Pipelined Table Function Inside a Package:
Unlike regular table function, pipelined table function can be defined using record and table types defined inside a package specification.
--DETERMINISTIC & PIPELINED TABLE Functions WITH RESULT CACHE: Example: Pipelined Table Function Inside a Package
create or replace package pipe_pkg
is
type emp_ot is record
(
EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25),
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE,
JOB_ID VARCHAR2(10),
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4)
);
type emp_nt is table of emp_ot;
function doubled_pl(row_in emp_pkg.rc1)
return emp_nt
pipelined;
end;
/
create or replace package body pipe_pkg
is
function doubled_pl(row_in emp_pkg.rc1)
return emp_nt
pipelined
is
type emp_t is table of employees%rowtype index by pls_integer;
t_emp emp_t;
begin
loop
fetch row_in bulk collect into t_emp limit 100;
exit when t_emp.count = 0;
for i in 1..t_emp.count
loop
pipe row(t_emp(i));
end loop;
end loop;
return;
end doubled_pl;
end pipe_pkg;
/
select * from table(pipe_pkg.doubled_pl(cursor(select * from employees)));
Pipelined Table Function With Ref Cursor Arguments:
A pipelined table function can accept any argument that a regular table function accept. A table function that accept ref cursor as argument can serve as a transformation function. It use ref cursor to fetch input rows, perform some transformation. After that pipeline the result out either through interface or native plsql.
Check Example 2 above for reference.
FUNCTION RESULT CACHE:
- In PLSQL function result cache, we specify that a plsql function is to be cached or rather its results are to be cached. Each time we call the function with a new set of parameters, oracle execute the function. Then it adds the result into the result cache and return the result to us. When we repeat the function call with same set of parameters, then oracle return the result from result cache. It doesn't re-execute the function. Under certain circumstances this caching behavior can results in a significant performance gain.
- Cache is a part of System Global Area (SGA). So its contents are available to all sessions connected to the instance. Oracle database will apply its recently used algorithm to the cache. Its doing this to ensure that most recently accessed data will be preserved in the cache.
- Prior to 11g, similar kind of caching was possible with package-level collections. But this cache is limited to a specific session and was part of Process Global Area (PGA). So if I have 100 different session running the application, a huge amount of memory will be used along with SGA memory.
- PLSQL function result cache minimizes the memory usages by sharing the data across the sessions. Two major benefits are:
- Low memory usage
- Automatic purge cached result whenever changes are committed.
PERFORMANCE COMPARISON WITH REGULAR FUNCTION:
Testing with a Regular Function:
--DETERMINISTIC & PIPELINED TABLE Functions WITH RESULT CACHE: Performance Comparison
create or replace function get_emp_details(p_empid employees.employee_id%type)
return varchar2
is
v_empid employees.employee_id%type := null;
v_name employees.last_name%type := null;
v_jobid employees.job_id%type := null;
v_sal employees.salary%type := null;
begin
begin
select employee_id,
last_name,
job_id,
salary
into v_empid,
v_name,
v_jobid,
v_sal
from employees
where employee_id = p_empid;
exception
when others then
v_empid := null;
v_name := null;
v_jobid := null;
v_sal := null;
end;
dbms_lock.sleep(1);
return ('Employee_id = '||v_empid||' Name = '||v_name||' Job = '||v_jobid||' Salary = '||v_sal);
exception
when others then
return('Error -> '||sqlerrm);
end get_emp_details;
/
Calling in a PLSQL Block:
1st Execution:
--DETERMINISTIC & PIPELINED TABLE Functions WITH RESULT CACHE: Example: Performance Comparison in a PLSQL Block
set serveroutput on;
declare
v_info varchar2(4000);
v_start pls_integer := 0;
v_end pls_integer := 0;
v_count pls_integer := 0;
begin
v_start := dbms_utility.get_time;
for i in (select employee_id from employees order by employee_id fetch first 10 rows only)
loop
v_info := get_emp_details(i.employee_id);
dbms_output.put_line('Employee Information := '||v_info);
v_count := v_count + 1;
end loop;
v_end := dbms_utility.get_time;
dbms_output.put_line('Time Elapsed : '||(v_end - v_start));
dbms_output.put_line('No of Record fetched = '||v_count);
end;
/
Output:
2nd Execution:
conn sys as sysdba/manager123
alter system flush shared_pool;
execute dbms_result_cache.flush;
conn hr/hr
Testing a RESULT_CACHE Function:
--DETERMINISTIC & PIPELINED TABLE Functions WITH RESULT CACHE: Example: RESULT_CACHE
create or replace function get_emp_details_cache(p_empid employees.employee_id%type)
return varchar2
result_cache
is
v_empid employees.employee_id%type := null;
v_name employees.last_name%type := null;
v_jobid employees.job_id%type := null;
v_sal employees.salary%type := null;
begin
begin
select employee_id,
last_name,
job_id,
salary
into v_empid,
v_name,
v_jobid,
v_sal
from employees
where employee_id = p_empid;
exception
when others then
v_empid := null;
v_name := null;
v_jobid := null;
v_sal := null;
end;
dbms_lock.sleep(1);
return ('Employee_id = '||v_empid||' Name = '||v_name||' Job = '||v_jobid||' Salary = '||v_sal);
exception
when others then
return('Error -> '||sqlerrm);
end get_emp_details_cache;
/
Calling in a PLSQL Block:
1st Execution:
declare
v_info varchar2(4000);
v_start pls_integer := 0;
v_end pls_integer := 0;
v_count pls_integer := 0;
begin
v_start := dbms_utility.get_time;
for i in (select employee_id from employees order by employee_id fetch first 10 rows only)
loop
v_info := get_emp_details_cache(i.employee_id);
dbms_output.put_line('Employee Information := '||v_info);
v_count := v_count + 1;
end loop;
v_end := dbms_utility.get_time;
dbms_output.put_line('Time Elapsed : '||(v_end - v_start));
dbms_output.put_line('No of Record fetched = '||v_count);
end;
/
Output:
2nd Execution:
See the differences between 1st execution and 2nd execution for Regular Function and Result_Cache Function. For 2nd execution Result_Cache function took no time. Clearly for 2nd execution result is came from Result Cache.
Now we update the employees table's salary column and check the result of the Regular function and Result_Cache function.
RELIES_ON Clause:
Upto 11g, if we update employees table and execute the Result_Cached function then the impact of update was not visible. This is due to the results are shown from Result_Cache. So 11g introduced RELIES_ON clause. It determine the invalidation of Result_Cache post modification of tables used after RELIES_ON clause.
- But RELIES_ON clause is deprecated in 12c. This is due to 12c database detects all the data sources that are queried while a Result_Cached function is running.
- RELIES_ON clause cannot be used in a function declared in an anonymous block.
update employees
set salary = salary * 1.1;
107 rows updated.
commit;
declare
v_info varchar2(4000);
v_start pls_integer := 0;
v_end pls_integer := 0;
v_count pls_integer := 0;
begin
v_start := dbms_utility.get_time;
for i in (select employee_id from employees order by employee_id fetch first 10 rows only)
loop
v_info := get_emp_details_cache(i.employee_id);
dbms_output.put_line('Employee Information := '||v_info);
v_count := v_count + 1;
end loop;
v_end := dbms_utility.get_time;
dbms_output.put_line('Time Elapsed : '||(v_end - v_start));
dbms_output.put_line('No of Record fetched = '||v_count);
end;
/
See updated salaries are visible and the Function is re-executed and Result_Cache is not been used.
2nd Execution after updation:
Now Result_Cache is used.
Restrictions on RESULT_CACHE:
- RESULT_CACHE is disallowed on function having OUT and IN OUT parameters
- RESULT_CACHE is disallowed on function having following datatypes in IN parameter or RETURN clause or anywhere in the body of the function:
- BLOB
- CLOB
- NCLOB
- REF CURSOR
- COLLECTION
- OBJECT
- RECORD or PLSQL collection that contains an unsupported return type
- RESULT_CACHE disallowed on function declared in an anonymous block.
- RESULT_CACHE disallowed on PIPELINED TABLE Function and Nested Function.
RESULT CACHE PARAMETERS:
The PLSQL function result cache and SQL result cache are managed together using the following parameters:
- RESULT_CACHE_MODE
- RESULT_CACHE_MAX_SIZE
- RESULT_CACHE_MAX_RESULT
- RESULT_CACHE_REMOTE_EXPIRATION
RESULT_CACHE_MODE:
There are two RESULT_CACHE_MODE:
- MANUAL
- FORCE
MANUAL: This is the default. This mode is added only when RESULT_CACHE hint is used.
FORCE: When this mode used then all the select statement will be cached. To disable the effect we have use NO_RESULT_CACHE hint.
conn sys as sysdba/manager123
ALTER SESSION SET RESULT_CACHE_MODE = MANUAL;
Or
ALTER SYSTEM SET RESULT_CACHE_MODE = MANUAL;
conn hr/hr;
ALTER SESSION SET RESULT_CACHE_MODE = MANUAL;
RESULT_CACHE_MAX_SIZE:
- This specifies the maximum amount of SGA memory (in bytes) to be used by RESULT_CACHE.
- Values of this parameter is greater than 0 and rounded upto next multiple of 32KB.
- If the value is 0 then this feature is disabled.
RESULT_CACHE_MAX_RESULT:
This specifies the percentage of RESULT_CACHE_MAX_SIZE a single result can use.
RESULT_CACHE_REMOTE_EXPIRATION:
- This specifies the no of minutes that a result using a remote object is allowed to remain valid.
- Setting this parameter value to 0 implies that result using remote object will not be cached.
Information about the result cache is displayed using the following views:
- V$RESULT_CACHE_STATISTICS
- V$RESULT_CACHE_MEMORY
- V$RESULT_CACHE_OBJECTS
- V$RESULT_CACHE_DEPENDENCY
