ss

Oracle Tips by Burleson

Guidelines for the use of partitioned indexes

Use local prefixed indexes whenever possible.
It is more expensive to scan a nonprefixed index, due to more index probes required.
Unique local nonprefixed indexes are not supported.
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.