Oracle built-in functions for SQL

One of the beneficial features about Oracle’s SQL is their great use of built-in functions.
If we look at all of the nice extensions to SQL such as

1. DECODE and
2. string translation functions,

it should be apparent that BIFs are an integral part of the Oracle SQL environment. In fact, there are so many BIFs that entire application programs can be written in vanilla, chocolate, rasberry, and butter pecan SQL. In short, understanding and using BIFs is a very integral part of Oracle tuning. Oracle has many built-in functions and Oracle also allows you to define your own function and make it a part of your own SQL dialect. You can see all built-in Oracle functions with this query:

 
select distinct 
 object_name 
 from
  all_arguments 
 where
  package_name = 'STANDARD'; 

Here are my notes on common SQL built-in functions:

add_months, avg, chr(n), concat(s1,s2), 
convert(char_to_convert, new_char_set, old_char_set), count(*), 
decode, floor, greatest(expression, expression...), grouping, 
initcap(char) string with initial capitals, instr, last_day(date)returns the last day of month in date, 
least(expression, expression...), length(char), lower(char) lpad(char, n[,padchar]), ltrim(char[,set]), 
max([distinct] expr), min([distinct] expr), mod(x,y), 
months_between(end_date, start_date), next_day(date,day_of_week),
nvl(expression, value_if_null), rank, replace(char, search_str[, replace_str]), round,
rowidtochar, rpad, rtrim, soundex(char),
substr, sys_context('namespace','attribute_name'), to_lob, to_number,
to_date, to_char, translate, trim,
trunc, upper, user, userenv

In Oracle SQL, some functions are considered legacy or have been deprecated in favor of newer, more standard alternatives.
From the above list, here are the functions which are considered legacy or have been deprecated.
Legacy Functions:

1. decode - Often replaced by the `CASE` statement, which provides more clarity and alignment with SQL standards.
2. nvl - `COALESCE` is generally preferred as it aligns with standard SQL and can accept more than two arguments.
3. userenv - Typically, `SYS_CONTEXT` is preferred as it offers more flexibility and security features.

Deprecated Functions:

1. rowidtochar - Oracle documentation suggests newer alternatives might be preferred, but as of the latest versions, it is still commonly used. However, its use is often discouraged in favor of direct operations on `ROWID`.
2. to_lob - This is used specifically for certain type conversions which are less frequently necessary with improvements in Oracle's handling of LOBs.

These functions are still supported for compatibility with older applications, but Oracle documentation and best practices suggest migrating to more modern equivalents where possible to ensure better compatibility with future versions of Oracle and other SQL databases.

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.

The optimizer determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query. This determination is an important step in the processing of any SQL statement and can influence execution time. A SQL statement can be executed in many different ways, including the following:

1. Full table scans
2. Index scans
3. Nested loops
4. Hash joins

The output from the optimizer is a plan that describes an optimum method of execution. The Oracle server provides the (CBO) cost-based and (RBO) rule-based optimization. In general, use the cost-based approach.
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 following series of images below illustrates.

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.

1. 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.
2. 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