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: