Function-based indexes in Oracle

Function-based indexes are still used in Oracle 19c and remain an important feature. They are widely utilized to optimize queries involving functions or expressions in `WHERE` clauses or `ORDER BY` clauses. Function-based indexes can greatly enhance performance by allowing Oracle to use indexed values instead of recalculating the results of functions or expressions for each row.
Key Points about Function-Based Indexes in Oracle 19c:

1. Purpose:
   • They are used to index expressions or function results, rather than just column values.
   • Ideal for cases where queries involve complex computations, transformations, or conditions applied to columns.
2. Syntax:
   • Create a function-based index with the following syntax:

     CREATE INDEX index_name ON table_name (function(column_name));
             

   • For example:

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

3. Prerequisites:
   • Ensure the QUERY_REWRITE_INTEGRITY parameter is set to a mode that allows the use of function-based indexes.
   • Example:

     ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
             

   • You also need appropriate privileges to create indexes.
4. Common Use Cases:
   • Case-insensitive searches:

     SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';
             

   • Indexing expressions involving arithmetic:

     CREATE INDEX idx_salary_bonus ON employees (salary * 1.1);
             

   • Filtering on date functions:

     CREATE INDEX idx_trunc_hiredate ON employees (TRUNC(hire_date));
             

5. Advantages:
   • Improves query performance by allowing indexed lookups instead of full table scans.
   • Supports deterministic functions, improving efficiency for complex queries.
6. Limitations:
   • Only deterministic functions can be used in function-based indexes. Non-deterministic functions (e.g., SYSDATE, USER) are not allowed.
   • The index adds overhead during INSERT, UPDATE, or DELETE operations since the indexed values need to be recalculated.
7. Enhancements in Oracle 19c:
   • Oracle 19c continues to support function-based indexes and enhances indexing with new features like automatic indexing, which can work in harmony with function-based indexes if they are determined to improve performance.

Example: If you frequently query a column using the `LOWER` function, you can create a function-based index:

CREATE INDEX idx_lower_email ON users (LOWER(email));

Query:

SELECT * FROM users WHERE LOWER(email) = 'example@domain.com';

This query will use the function-based index to avoid a full table scan.
Function-based indexes remain a powerful tool in Oracle 19c for optimizing queries with expressions and maintaining high database performance.
Function-based indexes were introduced in Oracle8. Oracle provides a wealth of functions such as 1) to_char, 2) decode, and 3) substr that can transform the existing values of a column. In Oracle8, one could specify these built-in functions directly into the index creation syntax, resulting in an index that contains a transformation of the data values in the table column. In addition, you may create your own functions and use them in the index definition.

Function-based indexes are particularly useful for indexing on sub-values of a DATE column.
For example, assume that we have a table with a DATE column:

While we may create an index on the STAT_DATE column, what would we do if we required an index only on the year portion of stats_date ? Prior to Oracle8, we would have needed to define a redundant YEAR column in our table to support an index. We could issue the following query to get the 1999 rows, but it requires an alter session statement to set the date format to YYYY:

The best alternative is to create an index based upon the character representation of the date column.

create index char_date_idx
on stats
( 
   to_char(stat_date, 'YYYY-MM-DD'),
   db_name
)
tablespace stat_ts
storage (initial 10m next 10);

We have created an index on the character representation of the DATE data type column. If we enter a query where

to_char(stats_date,'YYYY') = '1999',

the SQL optimizer will use the index to retrieve the 1999 rows.

Oracle introduced expression-based indexes to enhance the database's query performance and flexibility in handling complex data retrieval scenarios. This feature allows indexes to be created not just on the columns of a table, but on expressions involving those columns. The primary motivations and advantages for introducing expression-based indexes include:

1. Improved Query Performance: By allowing indexes on expressions, Oracle enables more efficient data access patterns, particularly for queries that frequently use expressions in their WHERE clauses or join conditions. This can significantly reduce the need for full table scans and speed up query execution.
2. Enhanced Functionality: Expression-based indexes support a wide range of expressions, including arithmetic operations, SQL functions, and more. This capability facilitates optimized access to data that is derived or calculated from base columns, without needing to store the derived data separately.
3. Optimization of Complex Queries: Complex queries, especially those involving calculations, transformations, or functions applied to column data, can benefit greatly from expression-based indexes. Indexing the result of an expression means that the database can directly access pre-computed values, thus accelerating query processing.
4. Increased Flexibility: Developers and DBAs gain increased flexibility in tuning the database for performance. They can create indexes that are closely aligned with the application's query patterns, thus ensuring that the optimizer has the best possible access paths available.
5. Support for Advanced Features: Expression-based indexes are crucial for advanced database features like function-based indexes, virtual columns, and JSON-related searches. They enable efficient querying and manipulation of structured and semi-structured data within the Oracle database.
6. Compatibility and Standards Compliance: The introduction of expression-based indexes aligns Oracle more closely with SQL standards and enhances its compatibility with applications developed with standards compliance in mind. This makes it easier to migrate applications to and from Oracle Database.

In summary, Oracle's introduction of expression-based indexes is a strategic enhancement aimed at providing superior performance, flexibility, and functionality for managing and querying data, especially in complex and data-intensive environments.