Function Based Index in Oracle

Function based Indexes are used for ensuring faster retrieval of data for a query which contain a function.

Function based index is used by Oracle Server to fetch query results more efficiently and quickly.

Instead of creating an index on columns like we do in other Indexes, we create an index on Function used in Query in Function based Index.

Syntax

CREATE INDEX index_name
ON table_name(function(column_name);

Here,

index_name – Name of Index that we are about to create.

table_name – Name of table on which index is created.

function(column_name) – Here, instead of mentioning the column we directly mention the function which is used in query. A function can contain one or more than one columns.

Let’s understand Function Based Index through these examples:

Oracle Function Based Index Example

We

SELECT * FROM intern_table
WHERE UPPER(first_name) = 'WALTER';

Result:

The query returns all the interns named ‘Walter’ in our Interns table.

Explain Plan

Let’s Explain for this query to understand the execution plan:

EXPLAIN PLAN FOR
SELECT * FROM intern_table
WHERE UPPER(first_name) = 'WALTER';

SELECT * FROM table (dbms_xplan.display)

Result:

Create Function Based Index

CREATE INDEX func_idx
ON intern_table(UPPER(first_name));

Result:

After creating the index, lets recheck the execution plan for above executed query to see if the data is fetched faster and if there’s any improvement in efficiency.

EXPLAIN PLAN FOR
SELECT * FROM intern_table
WHERE UPPER(first_name) = 'WALTER';

SELECT * FROM table (dbms_xplan.display)

Result:

This time the query performs an index range scan instead of full table scan and we did notice a slight improvement in performance for this query. The improvements are more noticeable on larger tables.

Drop Function Based Index

We use the following query for dropping the function based Query:

DROP INDEX func_idx;

Data Dictionary View for Function Based Index

We use following data dictionary views to get more relevant information about function based indexes:

SELECT INDEX_NAME, TABLE_NAME, INDEX_TYPE,
TABLE_OWNER, UNIQUENESS, COLUMN_NAME,
COLUMN_POSITION
FROM USER_INDEXES NATURAL JOIN USER_IND_COLUMNS
WHERE TABLE_NAME = 'INTERN_TABLE';

Result:

This data dictionary gives us all the important data about indexes in one snapshot.

We take a natural join of 3 data dictionary views to get this view, which tells us about the function or expression used in an index:

SELECT INDEX_NAME, TABLE_NAME, COLUMN_POSITION,
INDEX_TYPE,
TABLE_OWNER, TABLE_TYPE, UNIQUENESS,
COLUMN_NAME, COLUMN_EXPRESSION
FROM USER_INDEXES
NATURAL JOIN USER_IND_COLUMNS
NATURAL JOIN USER_IND_EXPRESSIONS
WHERE TABLE_NAME = 'INTERN_TABLE'

Result:

In this tutorial, we learned how to create an index on expression and how it is used for improving the performance metrics of a query.