Function-Based Index in Oracle Database

A function-based index is an index created on the result of a function or expression applied to one or more columns of a table. This allows the Oracle optimizer to use the index for queries that involve expressions or functions, improving query performance when such expressions are frequently used.

Why Use Function-Based Indexes?

Optimize Complex Queries: When queries frequently use functions or expressions on columns, a function-based index can speed up these queries.
Support Case-Insensitive Searches: You can create an index to support case-insensitive searches.
Efficient Use of Virtual Columns: You can index virtual columns that are derived from other columns.

Creating Function-Based Indexes

Syntax


CREATE INDEX index_name ON table_name (function_or_expression);

Example Scenarios

Scenario 1: Case-Insensitive Search

Assume you have a table EMPLOYEES with a column LAST_NAME. You frequently perform case-insensitive searches on LAST_NAME.

Step 1: Create the Function-Based Index


CREATE INDEX idx_last_name_upper
ON EMPLOYEES (UPPER(LAST_NAME));

Step 2: Use the Index in a Query


SELECT * 
FROM EMPLOYEES 
WHERE UPPER(LAST_NAME) = 'SMITH';

Explanation

Index Name: idx_last_name_upper is the name of the index.
Table Name: EMPLOYEES is the table.
Expression: UPPER(LAST_NAME) is the expression applied to the LAST_NAME column.

The optimizer can use idx_last_name_upper to quickly locate rows where the uppercase version of LAST_NAME matches 'SMITH'.

Scenario 2: Expression-Based Index

Assume you have a table ORDERS with columns QUANTITY and UNIT_PRICE, and you frequently query on the total price (QUANTITY * UNIT_PRICE).

Step 1: Create the Function-Based Index


CREATE INDEX idx_total_price
ON ORDERS (QUANTITY * UNIT_PRICE);

Step 2: Use the Index in a Query


SELECT *
FROM ORDERS
WHERE QUANTITY * UNIT_PRICE > 1000;

Explanation

Index Name: idx_total_price is the name of the index.
Table Name: ORDERS is the table.
Expression: QUANTITY * UNIT_PRICE is the expression applied to the columns.

The optimizer can use idx_total_price to efficiently find rows where the total price is greater than 1000.

Function-Based Index with Custom Function

You can also create function-based indexes using custom functions. Assume you have a function get_discount that calculates a discount based on some logic.

Step 1: Create the Function


CREATE OR REPLACE FUNCTION get_discount(quantity NUMBER, unit_price NUMBER) RETURN NUMBER IS
BEGIN
    RETURN (quantity * unit_price) * 0.1; -- 10% discount
END;

Step 2: Create the Function-Based Index


CREATE INDEX idx_discount
ON ORDERS (get_discount(QUANTITY, UNIT_PRICE));

Step 3: Use the Index in a Query


SELECT *
FROM ORDERS
WHERE get_discount(QUANTITY, UNIT_PRICE) > 50;

Using the Index with Virtual Columns

Oracle allows the creation of virtual columns which are expressions based on other columns in the table.

Step 1: Add a Virtual Column


ALTER TABLE ORDERS ADD (total_price AS (QUANTITY * UNIT_PRICE));

Step 2: Create the Index on the Virtual Column


CREATE INDEX idx_total_price_vc
ON ORDERS (total_price);

Step 3: Use the Index in a Query


SELECT *
FROM ORDERS
WHERE total_price > 1000;

Explanation

Virtual Column: total_price is a virtual column defined as the product of QUANTITY and UNIT_PRICE.
Index: idx_total_price_vc is created on the virtual column.

Verifying and Monitoring Index Usage

Check Index Usage

You can use the EXPLAIN PLAN command to see if the index is being used:


EXPLAIN PLAN FOR
SELECT * 
FROM EMPLOYEES 
WHERE UPPER(LAST_NAME) = 'SMITH';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Monitor Index Usage

You can query the V$SQL and V$SQL_PLAN views to monitor the usage of indexes.

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Function-Based Index in Oracle Database