Databases Reference
In-Depth Information
Note
Bitmap and bitmap join indexes are the focus of Chapter 3.
Function-Based Indexes
Function-based indexes are created with SQL functions or expressions in their definitions. Function-
based indexes allow index lookups on columns referenced by SQL functions in the
WHERE
clause of a
query. Here's an example of creating a function-based index:
create index cust_fidx1
on cust(upper(last_name));
These types of indexes are necessary because Oracle won't use a normal B-tree index when a query
references a column with a SQL function applied to it.
Note
Function-based indexes can be either B-tree, unique, or bitmap.
Indexed Virtual Column
An alternative to a function based index is to add a virtual column to a table and then create an index on
that virtual column. You'll have to test and determine whether a function-based index or an index on a
virtual column better suits your performance requirements.
Listed next is a brief example. Suppose you have an
INV
table created with a virtual column.
create table inv(
inv_id number
,inv_count number
,inv_status generated always as (
case when inv_count <= 100 then 'GETTING LOW'
when inv_count > 100 then 'OKAY'
end)
);
Now you can create a regular index on the virtual column.
create index inv_idx1
on inv(inv_status);
Note
Virtual columns are only available in Oracle Database 11g and higher.
