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.
The next module will introduce you to some basic SQL tuning tools.
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.
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.