In Oracle SQL and PL/SQL, deterministic functions are functions that always return the same result for a given set of input parameters. Oracle considers a function deterministic if it produces consistent outputs regardless of when or how often it is called, assuming the inputs remain the same. Declaring a function as deterministic allows Oracle to optimize performance by caching the function results or using them in function-based indexes.

Key Characteristics of Deterministic Functions:

Consistency: A deterministic function must always return the same output when given the same inputs.
Performance Optimization: Declaring a function as deterministic allows Oracle to optimize by caching results or enabling function-based indexing.
No Side Effects: Deterministic functions should not modify data or rely on external state (like database tables) that can change, as that would make them non-deterministic.

Example of a Deterministic Function

Suppose you need a function to calculate the area of a circle, given its radius. Since the area of a circle is always the same for a given radius, this is a perfect example of a deterministic function.

CREATE OR REPLACE FUNCTION calculate_circle_area(radius NUMBER)
RETURN NUMBER DETERMINISTIC IS
BEGIN
RETURN 3.14159 * radius * radius;
END;
/

Explanation:

The function calculate_circle_area takes a radius as input and calculates the area using the formula π * r^2.

The DETERMINISTIC keyword tells Oracle that this function will always return the same result for the same input value of radius.

This allows Oracle to cache the result if needed or use it in function-based indexes.

Using the Deterministic Function in SQL

You can call this function directly in your SQL queries:

SELECT calculate_circle_area(5) AS area FROM dual;

Output:

AREA

78.53975

Benefits of Declaring a Function as Deterministic

Function-Based Indexes:

When you declare a function as deterministic, Oracle allows you to create indexes based on the function’s output.

Example: If you had a function that converted a product name to lowercase, and you frequently searched for products in a case-insensitive manner, you could create a function-based index:

CREATE OR REPLACE FUNCTION to_lowercase(product_name VARCHAR2)
RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
RETURN LOWER(product_name);
END;
/

CREATE INDEX idx_lower_product_name ON products (to_lowercase(product_name));

This allows faster searching, e.g.:

SELECT * FROM products WHERE to_lowercase(product_name) = 'apple';

Query Caching:

Oracle may cache the output of deterministic functions, meaning that if the same input is encountered, the database engine can return the cached result without recalculating it.

This reduces processing time, especially in complex queries that call the same function multiple times.

Example of a Non-Deterministic Function

To contrast, a non-deterministic function might involve retrieving the current date and time or querying a database table:

CREATE OR REPLACE FUNCTION get_current_date()
RETURN DATE IS
BEGIN
RETURN SYSDATE;
END;
/

Since the function get_current_date returns the current date and time, the result can change every time you call it, even without changing the input parameters. Therefore, this is a non-deterministic function.

When to Use Deterministic Functions

When the calculation is expensive: If your function involves complex calculations, marking it as deterministic can allow Oracle to cache the result.

When indexing is needed: If you need to index the result of a function to speed up queries, you should declare the function as deterministic.

Summary

Deterministic functions in Oracle SQL and PL/SQL provide consistent outputs for given inputs and can help optimize performance. By declaring a function as deterministic, you enable Oracle to cache results, use them in function-based indexes, and optimize query performance. However, the function must not modify data or rely on data that might change to maintain this behavior.
.
.
.

Deep dive how it will work
.
.
.

When you mark the calculate_circle_area function as deterministic, Oracle can optimize by caching the result. If you call the function with the same input (radius = 5), Oracle can return the pre-computed result instead of recalculating it. This caching can improve performance, especially if the function involves complex calculations or is used frequently in queries.

This is particularly useful in scenarios like function-based indexes. When you create an index using a deterministic function, Oracle can store the pre-computed results in the index. This way, even if you run a query that involves that function, it can quickly fetch the result from the index instead of recalculating it each time.

Deterministic Functions in PLSQL

AREA