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 Oracle
BIFs[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