Lesson 1

As we discussed in the previous module, Oracle supplies a host of extensions to the ANSI standard, known as built-in functions, to allow easier access and translation of data. While there are dozens of built-in functions, our concern here is the impact of built-in functions upon Oracle performance, and the use of built-in functions to improve the performance of SQL queries.
While this is not internal database tuning in the strictest sense, the prudent use of Oracle SQL extensions can dramatically improve the processing speed of programs that contain embedded SQL.
In fact, many programmatic functions such as translating characters, re-formatting numbers and dates, and editing text can very often be done right in the SQL query. By placing the bulk of the work in the Oracle query, the programs that contain the SQL become both faster and more elegant.
This module will show you the most common Oracle built-in functions (BIFs), and show how they can assist with Oracle performance by quickly transforming row data. By the end of this module, you will be able to:

Oracle built-in functions can impact database performance during the SQL Tuning Process, depending on how they are used. Some functions are more efficient than others, and their performance can vary based on the context in which they are used. Here are some examples:

1. CASE and UPPER Functions: These functions are compiled in a low-level "C language" as a single module in the Oracle kernel, making them efficient and not causing any context switches between the two engines. However, there are some built-in functions like SYSDATE and USER that can have a different impact.
2. Native SQL Functions: Most native SQL functions are also compiled in the Oracle kernel, which means they are generally efficient and do not impact performance significantly when used appropriately.
3. User-Defined Functions (UDFs): These functions are created by users and can be less efficient than built-in functions. They can impact performance if they are complex or if they are used in a way that requires a lot of function calls.
4. Function Calls in WHERE Clauses: Using functions in WHERE clauses can impact performance because the function needs to be evaluated for each row in the table. This can prevent the optimizer from using indexes efficiently.
5. Optimizing Function Calls: To optimize performance, you can consider using the BULK COLLECT clause to fetch the entire result set into memory in a single operation, or use nested loops to process smaller subsets of data.

In general, the impact of Oracle built-in functions on database performance depends on how they are used and the specific context of the SQL query. To minimize performance impacts, it's important to understand the nuances of each function and how it interacts with the data and the query optimizer.

1. Describe the purpose of Oracle built-in functions
2. Use query overrides with Oracle SQL
3. Use the NVL built-in function with Oracle SQL
4. Use the DECODE built-in function with Oracle SQL
5. Describe the purpose of some advanced built-in functions

Next we will look at the Oracle built-in functions.

If a NULL argument is passed to a built-in function, a NULL value is returned except in the following cases. The function DECODE compares its first argument to one or more search expressions, which are paired with result expressions. Any search or result expression can be NULL. If a search is successful, the corresponding result is returned. In Example 3-1, if the column manager_id is NULL, DECODE returns the value 'nobody'.
Example 3-1 NULL Value as Argument to DECODE Function

SQL> DECLARE
2 manager VARCHAR2(40);
3 name employees.last_name%TYPE;
4 BEGIN
5 -- NULL is a valid argument to DECODE.
6 -- In this case, manager_id is NULL
7 -- and the DECODE function returns 'nobody'.
8
9 SELECT DECODE(manager_id, NULL, 'nobody', 'somebody'), last_name
10 INTO manager, name
11 FROM employees
12 WHERE employee_id = 100;
13
14 DBMS_OUTPUT.PUT_LINE
15 (name || ' is managed by ' || manager);
16 END;
17 /
King is managed by nobody
PL/SQL procedure successfully completed.