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
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:
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.