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.

• The ANSI Standards
  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:

Question: Is "Decode" ANSIi standard sql?

Answer: The answer is No. It is specific to Oracle.

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.

In Oracle RDBMS, when you query a column using a built-in function, the optimizer typically does not use an index because the function modifies the column values, preventing a direct index lookup.
If you need to use an index while querying a column that involves a built-in function, consider these approaches:

1. Function-Based Index (FBI)
   • Oracle allows you to create function-based indexes (FBI), which store the computed value of a function applied to a column.
   • If your queries frequently use a function on a column, creating an FBI can significantly improve performance.
   • Example:
     

     SELECT * FROM employees WHERE UPPER(name) = 'JOHN DOE';
     

   • Normally, an index on name won't be used because UPPER(name) changes the indexed values.
   • To fix this, create a function-based index:
     

     CREATE INDEX idx_upper_name ON employees (UPPER(name));
     

   • Now, Oracle can use the index when executing:
     

     SELECT * FROM employees WHERE UPPER(name) = 'JOHN DOE';
     

2. Virtual Columns + Index
   • Another approach is to use virtual columns combined with an index.
   • Virtual columns store the result of a function as a computed column without taking up extra storage.
   • Example:
     

     ALTER TABLE employees ADD upper_name AS (UPPER(name));
     CREATE INDEX idx_upper_name ON employees (upper_name);
     

   • Now, Oracle can use idx_upper_name for optimized queries:
     

     SELECT * FROM employees WHERE upper_name = 'JOHN DOE';
     

3. Indexing Expressions with Computed Values
   • If your query uses expressions like column + 10, create an index with the computed value:
     

     CREATE INDEX idx_salary ON employees (salary + 10);
     

   • Then, queries like:

             SELECT * FROM employees WHERE salary + 10 = 60000;
             

     will use the index.
4. Using Materialized Views with Indexes
   • If function-based indexes aren't suitable, consider materialized views with indexed computed columns.

Conclusion

• If your queries apply built-in functions to a column, the standard index won't be used.
• To enable index usage:
  • Create a function-based index.
  • Use virtual columns with an index.
  • Index expressions involving computed values.
  • Use materialized views when necessary.

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