Using BIFs with Oracle SQL
Oracle BIFs are especially useful for retrieving Oracle table columns where a transformation is required.
We generally see a BIF under the following conditions:
- Transforming characters: Oracle provides the
to_number, to_date, upper, lower, and
substr BIFs for transforming character data at retrieval time.
- Transforming dates: The to_char BIF is extremely useful for transforming Oracle date datatypes.
The to_char BIF is used with dates to extract specific days, months, and years.
Of course, you know that the remedy for the problem of non-index usage when a query contains a BIF is to create a function-based index to match the predicate in the where
clause of the SQL.
However, there are some subtle surprises when using BIFs. Let us take a closer look at each of these transformation types and see how they change SQL execution.
Using BIFs with Character Datatypes
With character datatypes, we commonly see BIFs used to transform character strings to remove case sensitivity. For example, here we can query on the last_name column without being concerned about case sensitivity:
where upper(last_name) = 'JONES' ;
Using BIFs with Date Datatypes
One of the most common uses of Oracle BIFs is the transformation of the Oracle date datatype.
As you know, the Oracle date datatype stores both the date and the time down to the hundredths of a second. Because of this high degree of precision, it is difficult to convert the date datatype to a character.
However, we do have the nls_date_format session variable, which can be used to change an Oracle SQL statement's display format for all dates. Surprisingly, the nls_date_format also affects the execution plan for SQL statements. To see, examine the following query to display all employees hired in January.
where to_char(hiredate,'MON') = 'JAN' ;