For more about data warehousing, see Chapter 10.
Function-based indexes
Function-based indexes were introduced in Oracle8i. A function-based index is just
like a standard B*-tree or bitmap index, except that youcan base the index on the
result of a SQL function, rather than just on the value of a column or columns.
Prior to Oracle8i, 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 caseinsensitive
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.
Figure 4-2. Bitmap index
Part number 1 2 3 4 5 6
... ...
Bitmap index on 'color'
color =
color =
color =
'BLUE'
'RED'
'GREEN'
0 0 0 1 0 0 .
Pages:
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225