Creating Multiple Indexes on the Same Set of Columns- Tables and Constraints

Multiple indexes can be on the same set of columns, but there must be something physically different about the index. For example, one index is created as a B-tree index, and the second, as a bitmap index.

Also, there can be only one visible index for the same combination and order of columns. Any other indexes created on that same set of must be declared invisible as shown here:

SQL> create index cust_idx2 on cust(first_name, last_name);

SQL> create bitmap index cust_bmx1 on cust(first_name, last_name) invisible;

Why would you want two indexes defined on the same set of columns? You might want to do this if you originally implemented B-tree indexes and now wanted to change them to bitmap—the idea being, you create the new indexes as invisible and then drop the original indexes and make the new indexes visible. In a large database environment, this would enable you to make the change quickly. See the section “Implementing Invisible Indexes” for more information.

Implementing Function-Based Indexes

Function-based indexes are created with SQL functions or expressions in their definitions. Sometimes, function-based indexes are required when queries use SQL functions. For example, consider the following query, which uses an SQL UPPER function:

SQL> select first_name from cust where UPPER(first_name) = ‘JIM’;

In this scenario there may be a normal B-tree index on the FIRST_NAME column, but Oracle will not use a regular index that exists on a column when a function is applied to it.

In this situation, you can create a function-based index to improve the performance of queries that use a SQL function in the WHERE clause. This example creates a function-based index:

SQL> create index cust_fnx1 on cust(upper(first_name));

Function-based indexes allow index lookups on columns referenced by functions in the WHERE clause of a SQL query. The index can be as simple as the preceding example, or it can be based on complex logic stored in a PL/SQL function.

Any user-created SQL functions must be declared deterministic before they can be used in a function-based index. Deterministic means that for a given set of inputs, the function always returns the same results. You must use the keyword DETERMINISTIC when creating a user-defined function that you want to use in a function-based index.

If you want to see the definition of a function-based index, select from the DBA/ALL/ USER_IND_EXPRESSIONS view to display the SQL associated with the index. If you are using SQL*Plus, be sure to issue a SET LONG command first; for example,

SQL> SET LONG 500

SQL> select index_name, column_expression from user_ind_expressions;

The SET LONG command in this example tells SQL*Plus to display up to 500 characters from the COLUMN_EXPRESSION column, which is of type LONG.