Oracle Data Structures - Oracle Essentials - page 108

Function-based indexes
Function-based indexes were introduced in Oracle8 i . A function-based index is just like
a standard B*-tree index, except that you can base the index on the result of a SQL
function, rather than just on the value of a column or columns.
Prior to Oracle8 i , if you wanted to select on the result of a function, Oracle retrieved
every row in the database, executed the function, and then accepted or rejected each
row. With function-based indexes you can simply use the index for selection, without
having to execute the function on every row, every time.
For example, without a function-based index, if you wanted to perform a case-
insensitive selection of data you would have to use the UPPER function in the WHERE
clause, which would retrieve every candidate row and execute the function. With a
function-based index based on the UPPER function, you can select directly from the
index.
As of Oracle Database 10 g , you can perform case- or accent-
insensitive queries; these queries provide another way to solve this
problem.
This capability becomes even more valuable when you consider that you can create your
own functions in an Oracle Database. You can create a very sophisticated function and
then create an index based on the function, which can dramatically affect the perfor‐
mance of queries that require the function.
Invisible indexes
Oracle Database 11 g introduces a new option for all of the index types we've discussed
in previous sections—the invisible index . Normally, all indexes are used by the optimizer,
which is described later in this chapter. You can eliminate an index from optimizer
consideration by taking the index offline or by deleting the index. But with both of these
methods you will have to take some actions to bring the index up to date when you
bring it back into the database environment.
But what if you want to just remove the index from optimizer consideration for a limited
time, such as when you are testing performance? With the invisible option, an index is
not considered as a possible step in an access path, but updates and deletes to the un‐
derlying data are still applied to the index.
Storage indexes
A storage index is a structure in Exadata Storage automatically created by the Exadata
Storage Server software. A storage index is used to track the highest and lowest values
for the most frequently accessed columns in a 1 MB section of storage. These high and
low values are stored in memory and used to determine if that 1 MB block should be