Index Techniques   «Prev  Next»
Lesson 8Function-based Indexes
ObjectiveCreate function-based Index

Function-based indexes in Oracle

Yes, function-based indexes are still supported in Oracle 19c, but their use is generally discouraged in favor of expression-based indexes. Here's a breakdown of the pros and cons to help you decide:

Function-based indexes:

Pros:
  • Can be useful for indexing complex expressions that involve multiple columns or functions.
  • Can improve performance for queries that use the indexed expression, especially if the expression computation is expensive.

Cons:
  • More complex to create and maintain than expression-based indexes.
  • Can be less efficient than expression-based indexes, especially for simpler expressions.
  • May not be used by the optimizer in all cases, even if they could potentially improve performance.

Expression-based indexes:

Pros:
  • Simpler to create and maintain than function-based indexes.
  • Can be more efficient than function-based indexes, especially for simpler expressions.
  • More likely to be used by the optimizer to improve query performance.

Cons:
Cannot be used for as wide a range of expressions as function-based indexes.
In summary:
  • If you need to index a complex expression, a function-based index may be your only option.
  • However, for simpler expressions, an expression-based index is generally preferred.
  • It's always a good practice to test both options and see which one performs better for your specific query.


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.

Using a function-based Index

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:

This shows sql code to create a table with a DATE column.
This shows sql code to create a table with a DATE column 
create table stats
( 
 stat_date DATE,
 db_name char(12),
 other_data char(200)
);

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:

This shows sql code to alter date format to YYYY.
This shows sql code to alter date format to YYYY. 
Alter session set nls_date_format = 'YYYY';
select * from stats
where stats_date = to_date(2019, '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.

Why Oracle introduced Expression Based Indexes

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.

SEMrush Software