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.
  

Ten Best Things About Indexing

To day I wanna share all that I know about indexing for beginners.

1. An index is a performance-tuning method of allowing faster retrieval of records.

2. It's easy and provides immediate value, because it's just a CREATE INDEX statement

3.It can be used to speed up existing applications without changing any of their logic or queries

4. It can be used to supply additional functionality to applications with very little cost

5. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

6.Because I/Os are expensive operations on a computer system, table scans are very expensive. Reducing the amount of data that is read from the disk is desirable. By reducing the amount of data that is read, you reduce system overhead. An index improves your performance by knowing exactly where the data is on disk and avoiding costly table scans, thus reducing I/O overhead.

6. Indexes are transparent to the end-user application.

7.Oracle automatically maintains the indexes during a DML operation on the table. Oracle Optimizer chooses the suitable index during a SQL SELECT operation.

8.Oracle manages the index for you, there are no additional commands you need to use to keep the index sync'd with the table.

9. It can allow the SQL optimizer to avoid large chunks of data blocks and narrow down the candidate blocks very quickly taking a query that takes minutes to run to less than a second to run.

10. an index is one of the best features of an RDBMS and will solve so many of your performance and scalability issues