Oracle BIF Drawbacks

Oracle RDBMS allows you to utilize an index to query a column that contains a built-in function, but it's important to understand the conditions and specific usage rules. Here's a brief overview of how you can achieve this using Oracle's functionality of Function-Based Indexes (FBIs).
Function-Based Indexes provide the mechanism to index computed columns in Oracle. This technique can dramatically increase the performance of queries that use functions in the WHERE clause by precomputing the function value for each row in the table and storing it in the index. When a query that uses the same function in the WHERE clause is run, Oracle uses the precomputed values stored in the index rather than recomputing the function for each row.
Here is a simple example of how to create a Function-Based Index:

CREATE  INDEX upper_last_name_idx 
ON employees (UPPER(last_name));

In the example above, an index upper_last_name_idx is created based on the UPPER function applied to the last_name column of the employees table. This index can then be used by Oracle when processing a query that filters on UPPER(last_name).
However, there are certain points to consider while using FBIs:

1. User Rights: To create a function-based index, you need the QUERY REWRITE privilege on the schema containing the table on which you are defining the index.
2. Session Parameter: The session parameter QUERY_REWRITE_ENABLED should be set to TRUE to allow Oracle to use function-based indexes.
3. Cost-Based Optimizer: Oracle's cost-based optimizer must be used. The rule-based optimizer does not use function-based indexes.
4. Deterministic Functions: If a user-defined function is used, it must be declared as DETERMINISTIC, which means that the function will return the same result each time it is called with the same set of input values.

It's important to carefully analyze your query workload and understand the trade-offs when deciding to use function-based indexes, as they can add some overhead for DML operations (INSERT, UPDATE, DELETE) due to the need to maintain the additional index.

The foremost drawback is that until Oracle8i you could not use an index to query a column that contained a BIF.
For example, assume that we have the LAST_NAME column of the CUSTOMER table indexed.
The following query would use the index:

Select * from customer 
where last_name like ‘A%’; 

SELECT * FROM customer 
WHERE last_name like to_upper(‘a%’);

Here is the code to create a custom SQL BIF. Note that the DETERMINISTIC clause is required for an SQL BIF. This is because you must tell the database that the function will always return the same output value when given the same input value.

CREATE  OR REPLACE FUNCTION 
  plus_tax(p_book_retail_price number) 
RETURN NUMBER DETERMINISTIC 
AS 
 price_plus_tax NUMBER(5,2); 
BEGIN 
 -- Tax is set at 7% 
 price_plus_tax := p_book_retail_price +  p_book_retail_price*.07;
 return price_plus_tax; 
END;