Expression Based Index With Examples

Expression based Indexes are used for optimizing the performance of queries which contain an expression.

We create an index on column to ensure faster retrieval of data from tables and Expression Based Indexes also ensures optimization of queries for better performance, just like other Indexes.

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

Syntax

CREATE INDEX index_name
ON table_name(expression);

Here,

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

table_name – Name of table on which index is created.

expression – Expression that we used in the query and on which Index will be created

Oracle in the background evaluates the result of expression for each row and creates an index based on the results obtained. Next time, we run a query containing an expression after creating an index, instead of going for full table scan, Oracle just goes through the index and returns those values for which the expression is TRUE.

Let’s understand this through Example.

Oracle Expression Based Index Example

Suppose, we want to find all interns who are earning more than 7000 and have commission more than 100, then we use this query:

SELECT * FROM intern_table
WHERE salary + commission > 9000;

Result:

The query returns all interns whose salary and commission when combined is greater than 9000.

Explain Plan

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

EXPLAIN PLAN FOR
SELECT * FROM intern_table
WHERE salary + commission > 9000;

SELECT * FROM table (dbms_xplan.display)

Result:

Create Expression Based Index

CREATE INDEX expr_idx
ON intern_table(salary + commission);

Result:

Once the Index has been created, lets use the following queries for fetching the execution plan of query that we used above, to see if there is any noticeable improvement in Cost consumption of CPU and execution time.

EXPLAIN PLAN FOR
SELECT * FROM intern_table
WHERE salary + commission > 9000;

SELECT * FROM table (dbms_xplan.display)

Result:

As we can see the Cost(% CPU) dropped from 3 to 2 from the time when we previously executed this query. There will be more noticeable improvement in retrieval of data as the size of rows in the table on which we perform these operations increases.

Drop Expression Based Index

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

DROP INDEX expr_idx;

Data Dictionary View for Expression Based Index

Expression based Index is an example of function based index and gets stored in user_index_expressions data dictionary view.

We can use the following query to get more information about the Expression based index that we just created:

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:

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.