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.

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.

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

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.