Pros and Cons of Indexing

Pros

• 
  Its easy to use/implement and provides immediate value
  
• It can be used to speed up existing applications without changing any of their logic or queries. Many orders of magnitude query improvement may be observed.
  
• It can be used to precompute complex values without using a trigger
  
• Can be created either as B*Tree or bitmap index
  
• Index can be built on an arithmetic expression or expression containing PL/SQL, package functions, C callout or SQL built-in functions
  
• Optimizer can estimate selectivity more accurately if the expressions are materialized in a function-based index. Range scan can be used for queries with expression in where clause and has index build on the expression used.
  
• Provides efficient linguistic collation to use NLS sort index
  
• Indexes can be created on object columns and REF columns by using methods defined for the object.
  

Cons

• You cannot direct path load the table with a function based index if that function was user written and requires the SQL ENGIRE. That means you cannot direct path load into a table indexed using my_soundex(x), but you could if it had indexed upper(x).
  
• It will affect the performance of inserts and updates. (Remember, you insert a row once, you query it thousands of times.)
  
• If the function you are indexing is a user written function and it returns a string, you may have to expose a view for the end users to use.