SQL - What is an Index?

What is an Index?

• An index is a performance-tuning method of allowing faster retrieval of records. 
• An index creates an entry for each value that appears in the indexed columns.By default, Oracle creates B-tree indexes.
• Some indexes are created implicitly through constraints that are placed on a table. For example, a column with the constraint that its values be unique causes Oracle to create a unique key index.
• Indexes don’t fix all performance problems and can actually degrade performance if used improperly.
  Always test all applications with any new index you create.

Example

select Employees.FIRST_NAME from employees where EMPLOYEES.SALARY >1000  and Employees.FIRST_NAME like 'M%';

Oracle indexing Types

1.             B-tree index,
2.             Bitmap indexes,
3.             Function-based indexes,
4.             index-only tables (IOTs);

How these indexes may dramatically increase the speed of Oracle SQL queries ?

B-tree

•       A tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic time.
•       The B-tree is a generalization of a binary search tree in that a node can have more than two children.
•       Indexes contain pointers (in the form of ROWIDs) to table data, This reduces I/O by eliminating the need to sequentially scan a table’s blocks when searching for a row (sequential scan of table blocks is called a "full table scan").