Lesson 2	Oracle built-in functions for SQL
Objective	Describe the purpose of built-in functions with Oracle SQL.

Oracle built-in functions for SQL

Oracle built in functions

Describe the purpose of built-in functions with Oracle SQL. Since this portion of the performance tuning series is primarily concerned with improving the performance of programs and queries, we will confine our discussion to those Oracle built-in functions that can be used to make Oracle SQL queries run faster, although there are many other built-in functions available within Oracle.
There are several advantages to using OracleBIFs^[1]. Foremost, BIFs are language extensions, and therefore execute very quickly, far faster than using an external procedural program to perform this function. The only disadvantage to BIFs is that they are intrinsic to Oracle and cannot be ported to other databases such as DB2 or Informix.

How do BIFs work?

Essentially, Oracle built-in functions (BIFs) are called immediately after the row data has been retrieved from the table and before the data has been displayed to the user, as the SlideShow below illustrates.

1) Oracle executes the query and begins to fetch the row data.
2) Oracle performs the requested function upon the column value.
3) Oracle returns the translated data to the requesting SQL statement

SQL Built In Functions

BIFs add power to SQL

The goal and purpose of the built-in functions is to make SQL queries do more than just deliver the data. Built-in functions can reformat and manipulate Oracle data in ways that would be very difficult to achieve in a procedural program.

BIF example

For example, the following query uses the built-in DECODE function to change row variable into more meaningful data:

Select
   Emp_name,
   To_char(hire_date, ‘DD-MON-YYYY’),
   DECODE (state_code, ‘AZ’, ’Arizona’,
                       ‘CA’, ’California’,
                       ‘NC’, ’North Carolina’,
                       ‘WY’, ’Wyoming’,
                             ’NONE’)
from employee;
Barrett   03-Mar-1996    California
Burleeze  04-JAN-1993    North Carolina
Dahlstrom 06-Feb-1999    NONE
3 rows selected.

Here, instead of performing cumbersome manipulation of the state_code to translate it into a meaningful name, we simply specify the translation rules to Oracle. This example also uses the TO_CHAR function to translate the hire_date column into a readable format.

BIFs that improve SQL speed

Several built-in functions can radically improve the speed to SQL requests. These include:

DECODE	Improves the speed of Oracle queries by translating a list of row values from one value to another value
NVL (Null Value)	Ensures that an Oracle query is assigned a character value to all queries that may contain a null value
TO_CHAR	Translates and re-formats date values
MONTHS_BETWEEN	Performs automatic date comparison to determine the elapsed time between dates
TRANSLATE	Translates one set of column values to another set of values

Oracle query override functions will be examined next.

[1]BIF: Extensions to standard Oracle SQL