Oracle Function-Based Index FAQs with examples

1. What is a Function-Based Index in Oracle?

A Function-Based Index is an index created on the result of a function or expression instead of directly on a column.

It improves the performance of queries that use functions in the WHERE clause.

Example:

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

2. Why do we use a Function-Based Index?

Function-Based Indexes are used to:

• Improve queries that use functions
• Avoid Full Table Scans
• Speed up case-insensitive searches
• Improve query performance

3. When should you use a Function-Based Index?

Use a Function-Based Index when queries frequently use functions like:

• UPPER()
• LOWER()
• TRUNC()
• NVL()
• SUBSTR()
• ROUND()

Example:

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

Without a Function-Based Index, Oracle may perform a Full Table Scan.

4. How do you create a Function-Based Index?

Syntax:

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

Example:

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

5. How does a Function-Based Index work?

Create the index:

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

Query:

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

Oracle can use the Function-Based Index instead of scanning every row.

6. Can we use built-in functions?

Yes.

Examples:

• UPPER()
• LOWER()
• TRUNC()
• NVL()
• SUBSTR()
• ROUND()
• ABS()

Example:

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

7. Can we use expressions?

Yes.

Example:

CREATE INDEX idx_total_salary
ON employees(salary + commission_pct);

Oracle indexes the result of the expression.

8. Can we create a Function-Based Index on multiple columns?

Yes.

Example:

CREATE INDEX idx_fullname
ON employees(UPPER(first_name || ' ' || last_name));

Query:

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

9. What is a common use of a Function-Based Index?

Case-insensitive searches.

Example without index:

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

Create index:

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

Now Oracle can use the index.

10. What happens if we use a normal index with a function?

Suppose a normal index exists:

CREATE INDEX idx_lastname
ON employees(last_name);

Query:

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

Oracle usually cannot use the normal index efficiently because the query applies a function to the indexed column.

11. How do you view Function-Based Indexes?

SELECT index_name,
       index_type
FROM user_indexes
WHERE index_type = 'FUNCTION-BASED NORMAL';

To view the indexed expression:

SELECT index_name,
       column_expression
FROM user_ind_expressions;

12. How do you drop a Function-Based Index?

DROP INDEX idx_upper_lastname;

13. What are the advantages of Function-Based Indexes?

• Faster searches using functions
• Improves case-insensitive queries
• Reduces Full Table Scans
• Improves query performance
• Supports indexing expressions

14. What are the disadvantages of Function-Based Indexes?

• Use additional storage
• Slow INSERT, UPDATE, and DELETE operations because the index must be maintained
• Queries must use the same function or compatible expression for Oracle to use the index effectively

15. Interview Example

Create a table:

CREATE TABLE employees (
  employee_id NUMBER,
  last_name   VARCHAR2(50)
);

Create the index:

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

Run the query:

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

Oracle can use the Function-Based Index to retrieve matching rows efficiently.

16. Real-World Example

Suppose users can search employee emails without worrying about letter case.

Query:

SELECT *
FROM employees
WHERE LOWER(email) = 'john@gmail.com';

Create the index:

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

This improves the performance of case-insensitive email searches.

17. Can a Function-Based Index be Unique?

Yes.

Example:

CREATE UNIQUE INDEX idx_unique_upper_email
ON employees(UPPER(email));

This ensures that email addresses are unique regardless of case.

Example: John@ABC.com and john@abc.com are treated as duplicates.

18. How do you check if Oracle is using a Function-Based Index?

Use an execution plan.

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

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

If the execution plan shows an index access path such as INDEX RANGE SCAN, Oracle is using the Function-Based Index.

Key Point:
Function-Based Indexes are ideal when queries apply functions to columns, especially for case-insensitive searches and expression-based filtering.