Function Based Index FAQS

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

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

Instead of indexing:

column_name

It indexes:

FUNCTION(column_name)

Example:

CREATE INDEX emp_upper_name_idx

ON employees(UPPER(last_name));

2. Why do we need Function-Based Index?

Normally, Oracle cannot use a normal index if a function is applied in the WHERE clause.

Example:

SELECT *

FROM employees

WHERE UPPER(last_name) = 'SMITH';

If there is only a normal index on last_name, Oracle will likely perform a Full Table Scan.

With Function-Based Index:

CREATE INDEX emp_upper_name_idx

ON employees(UPPER(last_name));

Now Oracle can use the index.

3. How does Function-Based Index work internally?

Oracle:

1.    Evaluates the function result

2.    Stores the computed value in the index leaf blocks

3.    Uses it during search

Internally still a B-Tree index (unless bitmap specified).

Structure:

Computed Value → ROWID

4. When should you use Function-Based Index?

Use when:

• Queries frequently use functions in WHERE clause
• Case-insensitive searches
• Date truncation
• Mathematical expressions
• Business rule expressions

Examples:

• UPPER(name)
• TRUNC(order_date)
• salary * 12
• NVL(status, 'N')

5. Case-Insensitive Search Example

Problem:

SELECT *

FROM users

WHERE UPPER(username) = 'JOHN';

Solution:

CREATE INDEX users_upper_idx

ON users(UPPER(username));

Improves performance dramatically.

6. Date Function Example

Problem:

SELECT *

FROM orders

WHERE TRUNC(order_date) = DATE '2025-02-23';

Normal index on order_date won’t be used efficiently.

Solution:

CREATE INDEX orders_trunc_date_idx

ON orders(TRUNC(order_date));

7. Mathematical Expression Example

CREATE INDEX emp_annual_salary_idx

ON employees(salary * 12);

Query:

SELECT *

FROM employees

WHERE salary * 12 > 100000;

Now index can be used.

8. Can Function-Based Index be UNIQUE?

Yes.

Example:

CREATE UNIQUE INDEX users_upper_email_uk

ON users(UPPER(email));

Prevents:

• 'john@email.com'
• 'JOHN@email.com'

from being inserted as duplicates.

9. What are requirements before creating FBI?

You must enable:

ALTER SESSION SET query_rewrite_enabled = TRUE;

And:

ALTER SESSION SET query_rewrite_integrity = TRUSTED;

Also:

• Function must be deterministic (for user-defined functions)
• User-defined functions must use DETERMINISTIC keyword

10. What is Deterministic Function?

A function that:

• Returns same output for same input
• Has no side effects

Example:

CREATE OR REPLACE FUNCTION get_tax(p_salary NUMBER)

RETURN NUMBER DETERMINISTIC IS

BEGIN

   RETURN p_salary * 0.1;

END;

Then:

CREATE INDEX emp_tax_idx

ON employees(get_tax(salary));

11. What are performance benefits?

• Avoids Full Table Scan
• Enables Index Range Scan
• Faster case-insensitive lookups
• Optimizes computed filters
• Improves large-table query performance

12. What are disadvantages?

• Extra storage
• Slows down INSERT/UPDATE
• Expression must match exactly in query
• Function must be deterministic

13. Important Rule: Expression Must Match Exactly

Index:

CREATE INDEX emp_upper_idx

ON employees(UPPER(last_name));

Query must use:

WHERE UPPER(last_name) = 'SMITH';

If query uses:

WHERE LOWER(last_name) = 'smith';

Index will NOT be used.

Expression must match.

14. Can it be Composite?

Yes.

CREATE INDEX emp_complex_idx

ON employees(UPPER(last_name), TRUNC(hire_date));

Used when both expressions appear in WHERE clause.

15. Function-Based Index vs Normal Index

Feature	Normal Index	Function-Based Index
Indexes raw column	Yes	No
Indexes expression	No	Yes
Case-insensitive support	No	Yes
Extra DML overhead	Moderate	Slightly more
Storage	Standard	Slightly larger

16. How does DML affect Function-Based Index?

When inserting/updating:

1.    Oracle computes function value

2.    Stores in index

3.    Maintains index structure

If function complex → slight performance impact.

17. Real-world example

Application requirement:

Search customers by:

WHERE NVL(status, 'N') = 'A';

Solution:

CREATE INDEX cust_status_idx

ON customers(NVL(status, 'N'));

Now query is optimized.

18. Common mistakes

• Forgetting DETERMINISTIC for user functions
• Using non-deterministic functions (SYSDATE, RANDOM)
• Expression mismatch
• Overusing FBI unnecessarily
• Not gathering statistics

19. Performance tuning tips

• Use for frequently executed queries
• Keep expression simple
• Avoid heavy computational functions
• Gather statistics after creation
• Avoid too many FBIs on same table
• Use only when normal index cannot help

20. Interview Tip

If asked:

“What is a Function-Based Index and why would you use it?”

Answer:

“A Function-Based Index stores the result of a function or expression in the index rather than the raw column value. It allows Oracle to use an index when queries apply functions in the WHERE clause, such as UPPER, TRUNC, or mathematical expressions. It improves query performance but adds some DML overhead and requires deterministic functions.”