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

Function-based Indexes in Oracle

Function-based indexes, introduced in Oracle8, remain a vital feature in Oracle 19c for optimizing queries involving functions or expressions in `WHERE` or `ORDER BY` clauses. They enhance performance by allowing Oracle to use precomputed indexed values instead of recalculating function or expression results for each row. Key Points about Function-Based Indexes in Oracle 19c:
  1. Purpose:
    • Indexes expressions or function results, not just column values.
    • Ideal for queries involving complex computations, transformations, or conditions on columns.
  2. Syntax:
    • Create a function-based index with: 
      CREATE INDEX index_name ON table_name (function(column_name));
    • Example: 
      CREATE INDEX idx_upper_lastname ON employees (UPPER(last_name));
  3. Prerequisites:
    • Ensure the QUERY_REWRITE_INTEGRITY parameter allows function-based indexes: 
      ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
    • Appropriate index creation privileges are required.
  4. Common Use Cases:
    • Case-insensitive searches: 
      SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';
    • Indexing arithmetic expressions: 
      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 enabling indexed lookups, reducing full table scans.
    • Supports deterministic functions for efficient complex query execution.
    • Enhances flexibility by aligning indexes with application query patterns, optimizing data access.
    • Facilitates advanced features like virtual columns and JSON searches.
    • Aligns with SQL standards, improving application compatibility.
  6. Limitations:
    • Only deterministic functions are allowed (e.g., SYSDATE, USER are not permitted).
    • Adds overhead during INSERT, UPDATE, or DELETE operations due to index recalculation.
  7. Enhancements in Oracle 19c:
    • Supports integration with automatic indexing, which can complement function-based indexes for performance gains.
Example: For frequent queries using the LOWER function, 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 leverages the index to avoid a full table scan. Oracle provides built-in functions like TO_CHAR, DECODE, and SUBSTR for transforming column values in index definitions. Custom user-defined functions can also be used, provided they are deterministic.

Using a Function-Based Index

Function-based indexes are particularly effective for indexing sub-values of a DATE column. For example, consider a table with a DATE column:
SQL code to create a table with a DATE column for statistical data.
SQL code to create a `stats` table with a `stat_date` DATE column for storing statistical data. 
create table stats
( 
 stat_date DATE,
 db_name char(12),
 other_data char(200)
);
To index only the year portion of `stat_date`, pre-Oracle8 solutions required a redundant YEAR column. A less efficient approach involved altering the session date format:
SQL code to query stats by year using altered date format.
SQL code to query the `stats` table for 2019 records by altering the session date format to 'YYYY'. 
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY';
SELECT * FROM stats WHERE stat_date = TO_DATE(2019, 'YYYY');
A better solution is to create a function-based index on the character representation of the date: 
CREATE INDEX char_date_idx
ON stats
( 
   TO_CHAR(stat_date, 'YYYY-MM-DD'),
   db_name
)
TABLESPACE stat_ts
STORAGE (INITIAL 10M NEXT 10);
This index enables efficient queries like: 
SELECT * FROM stats WHERE TO_CHAR(stat_date, 'YYYY') = '1999';
The SQL optimizer uses the index to retrieve the 1999 rows, avoiding a full table scan.

SEMrush Software