Oracle Tips by Burleson
for the use of partitioned indexes
Use local prefixed indexes whenever
It is more expensive to scan a
nonprefixed index, due to more index probes required.
Unique local nonprefixed indexes are not
DML operations on global unique indexes
are not supported in parallel update.
Global prefixed indexes can minimize the
number of index probes.
Using Function-Based Indexes
Oracle8i also introduced the concept of a
function-based index. In previous releases of Oracle, if we wanted
to have a column that was always searched uppercase (for example, a
last name that could have mixed-case, such as McClellum), we had to
place the returned value with its mixed-case letters in one column
and add a second column that was uppercased to index and use in
searches. The double storage of columns required for this type of
searching led to the doubling of size requirements for some
application fields. The cases where more complex requirements such
as the use of SOUNDEX and other functions would also have required
the use of a second column. This is not the case with Oracle
releases later than and including Oracle8i; Oracle-provided
functions, user-defined functions, as well as methods, can be used
in indexes. Let’s look at a simple example using the UPPER function.
See Code Depot
In many applications, a column may store a
numeric value that translates to a minimal set of text values; for
example, a user code that designates functions such as ‘Manager’,
‘Clerk’, or ‘General User’. In previous versions of Oracle, you had
to perform a join between a lookup table and the main table to
search for all ‘Manager’ records. With function indexes, the DECODE
function can be used to eliminate this type of join.