Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy




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.

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks