Creating function-based Index in Oracle

Although the function-based index is new to Oracle, you create a function-based index in the same way that you create a standard index, as shown in the following MouseOver. The first part of the CREATE INDEX statement is exactly like the standard form of the statement. After defining a name for the index and a table the index is associated with, you simply define the function on which the index will be based, rather than naming the columns that make up the index.

In Oracle databases, a Function-based Index is created to improve the performance of queries that use functions in the WHERE clause. These indexes are especially useful for speeding up queries that involve calculations, case conversions, or other function operations on column data. Here's an example of creating a Function-based Index: Suppose you have a table named `employees` with a column `hire_date` that stores the dates employees were hired. You frequently run queries that involve searching for employees hired in a specific month and year, which requires extracting the month and year from the `hire_date` column. A Function-based Index can optimize these queries. The syntax for creating such an index would be:

CREATE INDEX idx_hire_month_year ON employees
(EXTRACT(MONTH FROM hire_date), EXTRACT(YEAR FROM hire_date));

In this example:

• `idx_hire_month_year` is the name given to the Function-based Index.
• `employees` is the name of the table on which the index is being created.
• `EXTRACT(MONTH FROM hire_date)` and `EXTRACT(YEAR FROM hire_date)` are the function expressions used. These expressions extract the month and year respectively from the `hire_date` column.

With this index in place, queries that filter results based on the month and year of the `hire_date` will be more efficient. For instance, a query like:

SELECT * FROM employees 
WHERE EXTRACT(MONTH FROM hire_date) = 6 
AND EXTRACT(YEAR FROM hire_date) = 2023;

will benefit significantly from the `idx_hire_month_year` index, leading to potentially faster query execution times. This example illustrates the utility of Function-based Indexes in optimizing database performance, especially for queries that involve complex conditions based on column transformations or calculations.

The Oracle cost-based optimizer will use a function-based index if it can be used as part of the execution path. Just as with the materialized view, discussed in the previous module, the use of a function-based index is transparent to the user. However, to guarantee that the function-based index will be used, you should also include a condition that guarantees that the result of the function in the query will not return a NULL value. For instance, if you created a function-based index based on applying the UPPER() function to the NAME column, the following SQL statement would use the function-based index.

SELECT * FROM EMP WHERE UPPER(NAME) > 
'GREEN' AND UPPER(NAME) IS NOT NULL;

You can use more than just a simple function like UPPER() in a function-based index. A function-based index can use a more complex arithmetic calculation or even a PL/SQL function that you define yourself. You cannot use an aggregate function, such as SUM(), for a function-based index. Because the function-based index is recognized only by the cost-based optimizer, you must gather statistics on the index with the ANALYZE command before it will be used by the optimizer. The next lesson is the module wrap-up.

You can create indexes on functions and expressions that involve one or more columns in the table being indexed. A function-based index computes the value of a function or expression involving one or more columns and stores it in the index. A function-based index can be either a B-tree or a bitmap index. The function used for building the index can be an arithmetic expression or an expression that contains a SQL function, user-defined PL/SQL function, package function, or C callout. For example, a function could add the values in two columns.

Click the Exercise link below to practice creating an index-organized table.
Create Function Based - Exercise