Oracle SQL enhances Standard SQL

Unlike access languages for non-relational databases, using SQL for queries has several important features. Being declarative, the person who writes an SQL statement is not concerned about how the Oracle engine will service the query. That is, the programmer is not concerned about whether Oracle uses an index^[1], performs a hash join^[2], or does a full-table scan^[3] against the table. The Oracle engine takes care of figuring out the optimal way to access the data when the SQL statement is executed.

Most of the major relational database vendors proclaim that their SQL is 100% ANSI^[4] standard. While this is true in most cases, the database vendors always add additional features and functionality to distinguish their product. Oracle is no exception. Oracle SQL has added many useful, but non-standard extensions to their implementation of the ANSI SQL standard. The Oracle dialect of SQL includes additional features that can greatly improve the speed of SQL queries. These extensions are commonly referred to as built-in functions (called BIF^[5]).

1. Performance extensions (SQL hints, parallel queries)
2. Data transformation extensions
   (null values (NVL), decode)
3. Formatting extensions (to_char, to_date, to_upper)

These extensions are unique to Oracle SQL. Applications that are written to be portable between databases must not use any of the vendor-supplied extensions. The portable SQL application system is specifically designed to be generic so it can be moved between relational database products without re-writing the SQL statements. For those of you who have asked the question:

These extensions are very helpful in quickly retrieving and formatting data, but they also have drawbacks to the database. We will exam ANSI standards in more detail later in the course. In the next lesson, the basic tools for Oracle tuning will be discussed.

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%’; 

This query, because it uses a BIF, cannot use the index:

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;

[1]index: This is a data structure used to facilitate fast access to table rows in a specified sequence.

[2]Hash join: This is an execution plan that creates a hash table in SGA memory and uses this memory structure to join the tables.

[3]Full-table scan: This is an execution plan that accesses a table without an index, reading each block of the table.

[4]ANSI standard:The American National Standards Institute.

[5]BIF:Extensions to standard Oracle SQL