Database Reference
In-Depth Information
EODA@ORA12CR1> analyze index processed_flag_idx
2 validate structure;
Index analyzed.
EODA@ORA12CR1> select name, btree_space, lf_rows, height from index_stats;
NAME BTREE_SPACE LF_ROWS HEIGHT
-------------------- ----------- ---------- ----------
PROCESSED_FLAG_IDX 14528892 1000000 3
Any retrieval via this index would incur three I/Os to get to the leaf blocks. This index is not only wide, but also
tall. To get the first unprocessed record, we will have to perform at least four I/Os (three against the index and one
against the table).
How can we change all of this? We need to make it so the index is much smaller and easier to maintain (with less
runtime overhead during the updates). Enter the function-based index, which allows us to simply write a function
that returns NULL when we don't want to index a given row and returns a non-NULL value when we do. For example,
since we are interested just in the
N
records, let's index just those:
EODA@ORA12CR1> drop index processed_flag_idx;
Index dropped.
EODA@ORA12CR1> create index processed_flag_idx
2 on big_table( case temporary when 'N' then 'N' end );
Index created.
EODA@ORA12CR1> analyze index processed_flag_idx validate structure;
Index analyzed.
EODA@ORA12CR1> select name, btree_space, lf_rows, height from index_stats;
NAME BTREE_SPACE LF_ROWS HEIGHT
-------------------- ----------- ---------- ----------
PROCESSED_FLAG_IDX 32016 1272 2
That is quite a difference—the index is some 32KB, not 14MB. The height has decreased as well. If we use this
index, we'll perform one less I/O than we would using the previous taller index.
Implementing Selective Uniqueness
Another useful technique with function-based indexes is to use them to enforce certain types of complex constraints.
For example, suppose you have a table with versioned information, such as a projects table. Projects have one of two
statuses: either
ACTIVE
or
INACTIVE
. You need to enforce a rule such that “Active projects must have a unique name;
inactive projects do not.” That is, there can only be one active “project X,” but you could have as many inactive project
Xs as you like.
The first response from a developer when they hear this requirement is typically, “We'll just run a query to see if
there are any active project Xs, and if not, we'll create ours.” If you read Chapter 7, you understand that such a simple
implementation cannot work in a multiuser environment. If two people attempt to create a new active project X at the
same time, they'll both succeed. We need to serialize the creation of project X, but the only way to do that is to lock the
entire projects table (not very concurrent) or use a function-based index and let the database do it for us.
