Oracle and PL/SQL Recipes: A Problem-Solution Approach

4-3. Optimizing a Function That Will Always Return the Same Result for a Given Input

Problem

You want to create a function that will return the same result whenever a given input, or set of inputs, is presented to it. You want the database to optimize based upon that deterministic nature.

Solution

Specify the DETERMINISTIC keyword when creating the function to indicate that the function will always return the same result for a given input. For instance, you want to return a specific manager name based upon a given manager ID. Furthermore, you want to optimize for the fact that any given input will always return the same result. The following example demonstrates a function that does so by specifying the DETERMINISTIC keyword:

CREATE OR REPLACE ...