The purpose of this module has been to introduce you to built-in functions (BIFs) to greatly improve the productivity of SQL statements.
Now that you have completed this module, you should be able to:
Describe the purpose of Oracle built-in functions
Use query overrides with Oracle SQL
Use the NVL built-in function with Oracle SQL
Use the DECODE built-in function with Oracle SQL
Describe the purpose of some advanced built-in functions
Here are the terms from this module that may have been new to you:
Built-in functions: Extensions to standard Oracle SQL
Outer join: This is a join whereby rows that do not have a match in both tables are also displayed.
Query Transformer
For some statements, the query transformer determines whether it is advantageous to rewrite the original SQL statement into a semantically equivalent SQL statement with a lower cost. When a viable alternative exists, the database calculates the cost of the alternatives separately and chooses the lowest-cost alternative. The following graphic shows the query transformer rewriting an input query that uses OR into an output query that uses UNION ALL.
Figure 3-7 Query Transformer
SELECT *
FROM sales
WHERE prod_id=136
UNION ALL
SELECT *
FROM sales
WHERE promo_id=33
AND LNNVL (prod_id=136);
Adaptive Query Plan
An execution plan that changes after optimization because run-time conditions indicate that optimizer estimates are inaccurate. An adaptive query plan has different built-in plan options. During the first execution, before a specific subplan becomes active, the optimizer makes a final decision about which option to use. The optimizer bases its choice on observations made during the execution up to this point. Thus, an adaptive query plan enables the final plan for a statement to differ from the default plan.
fixed plan: An accepted plan that is marked as preferred, so that the optimizer considers only the fixed plans in the SQL plan baseline. You can use fixed plans to influence the plan selection process of the optimizer.
The next module will introduce you to some basic SQL tuning tools.
Oracle SQL built-in function known as "NVL" is available in Oracle 13c
The NVL function is still available in Oracle 13c.
It provides the following features:
Core Function: NVL has been a part of Oracle SQL for a very long time. It's a fundamental function used for handling null values.
Documentation: You can find the NVL function documented in the official Oracle 13c reference: [invalid URL removed]
Purpose: NVL is frequently used to replace NULL values with a default value, preventing errors and making calculations more predictable.
