Oracle Query Override Functions

You can override queries by specifying directives that reformat the information. For example, suppose that you need to query the employee table and determine the minimum, average, and highest salary. Without BIFs, you would need to read every salary column from the employee table, store the values in an internal array, and then compute the values. Using BIFs, you can quickly answer the question this way:

Select
  Min(salary),
  Avg(salary),
  Max(salary)
From
   Employee;

Here we have replaced a cumbersome coding task with a simple SQL statement. We are using the MIN, MAX, and AVG BIFs to quickly compute the minimum, maximum, and average salary for employees.

You can use Oracle Built-in Functions (BIFs) to override Oracle SQL queries by employing them to perform specific operations, which can enhance query performance, reformatting, and data manipulation. Here's a summary of how to do this:

1. Scalar Subquery Caching: You can use scalar subquery caching to improve the performance of function calls in SQL. This method caches the results of a subquery, allowing Oracle to avoid calling the function multiple times.
2. DETERMINISTIC Hint: The DETERMINISTIC hint can be used to inform Oracle that a function always gives the same output for the same input parameters. This allows Oracle to cache the function's result, improving performance.
3. Cross-Session PL/SQL Function Result Cache: Oracle 11g introduced the ability to cache the results of PL/SQL functions in a cross-session manner, further improving performance in certain scenarios.
4. Manual Caching Using PL/SQL Collections: You can manually cache the results of a function using PL/SQL collections, such as associative arrays, to store and retrieve function results. This can be particularly useful when the number of unique parameter signatures is low compared to the total number of rows processed.
5. Manual Caching Using Contexts: Another manual caching approach is to use contexts, which are session-specific and can store and retrieve function results.
6. Built-in Functions for Data Manipulation and Reformatting: Oracle provides a range of built-in functions (BIFs) that can be used to manipulate and reformat data within a SQL query. Examples include NVL for handling null values, TO_CHAR for date reformatting, MONTHS_BETWEEN for date comparison, and TRANSLATE for translating one set of values to another.
7. Pipelined Table Functions: These allow you to stream data in a table format, which can be useful for processing large datasets and improving query performance.

By incorporating these techniques and built-in functions, you can effectively override Oracle SQL queries to achieve better performance, data manipulation, and reformatting.

Consider a requirement to write a query that computes the number of months from today until an employee is eligible to retire. Using the MONTHS_BETWEEN BIF, we can quickly compute the number of months between today and the retirement data without having to write a cumbersome program:

SELECT MONTHS_BETWEEN
  (TO_DATE('02-02-2003','MM-DD-YYYY'),
   SYSDATE ) "Months"
 FROM DUAL;
    Months
----------
49.0322581

Hopefully these illustrations demonstrate how the powerful nature of Oracle BIFs can greatly improve the performance of Oracle queries.
Let us now look at how the null value clause can be used to improve query performance and accuracy.