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:

1. Transforming characters: Oracle provides the to_number, to_date, upper, lower, and substr BIFs for transforming character data at retrieval time.
2. 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.

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:

select customer_stuff
from
customer
where upper(last_name) = 'JONES' ;

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.

select ename
from emp 
where to_char(hiredate,'MON') = 'JAN' ;