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.

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.