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
. 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.
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.
For example, the following query uses the built-in DECODE function to change row variable into more meaningful data:
DECODE (state_code, ‘AZ’, ’Arizona’,
‘NC’, ’North Carolina’,
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.
Several built-in functions can radically improve the speed to SQL requests. These include:
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
Translates and re-formats date values
Performs automatic date comparison to determine the elapsed time between dates
Translates one set of column values to another set of values
Oracle query override functions will be examined next.