Database Indexes

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space.By default, Oracle creates B-tree indexes.

Types of Index

A)Clustered Index

B)Non Clustered Index

Clustered Index:- Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table.

• Typically created on primary key column.

Non Clustered Index:-The data is present in random order, but the logical ordering is specified by the index. The data rows may be randomly spread throughout the table. The non-clustered index tree contains the index keys in sorted order, with the leaf level of the index containing the pointer to the page and the row number in the data page

• The physical order of the rows is not the same as the index order.
• Typically created on column used in JOIN, WHERE, and ORDER BY clauses.

Difference between Clustered and non Clustered Index:-

1)Clustered indices can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected.Non Clustered index is Good for tables whose values may be modified frequently.

2) Clustered index usually saved in side the table however non Clustered Index saved in separate tables in database.

There are multiple ways to implement Index:-

1)Bitmap Index:-A bitmap index is a special kind of index that stores the bulk of its data as bitmaps and answers most queries by performing bitwise logical operations on these bitmaps. The most commonly used index, such as B+tree are most efficient if the values it indexes do not repeat or repeat a smaller number of times

2)B-tree Index:-By default, the Oracle creates a b_tree index.  In a b-tree, you walk the branches until you get to the node that has the data you want to use.  In the classic b-tree structure, there are branches from the top that lead to leaf nodes that contain the data.

3)Dense Index:- A dense index in database is a file with pairs of keys and pointers for every record  in the data file. Every key in this file is associated with a particular pointer to a record in the sorted data file. In clustered indices with duplicate keys, the dense index points to the first record with that key.

4)Sparse Index:-A sparse index in databases is a file with pairs of keys and pointers for every block in the data file. Every key in this file is associated with a particular pointer to the block in the sorted data file. In clustered indices with duplicate keys, the sparse index points to the lowest search key in each block. primary key is a sparse index.

5)Reverse Index:- reverse key index reverses the key value before entering it in the index. E.g., the value 24538 becomes 83542 in the index. Reversing the key value is particularly useful for indexing data such as sequence numbers, where new key values monotonically increase.

6)Function based Index:- An index is defined on the result of a function applied to one or more columns of a single table. Functional indexes can be used to obtain fast access to data based on the result of function calls.

Ex:-This query can use an index, if one has been defined on the result of the lower(coll) operation:

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

The function in the index definition can take more than one argument, but they must be table columns, not constants. Functional indexes are always single-column (namely, the function result) even if the function uses more than one input field; there cannot be multi column indexes that contain function calls.

Advantage of Index:- 

1. Faster retrieval of data. Increased performance.There is no need to access a row in the database from an index structure, so you can reduce the total number of I/O operations needed to retrieve data.
   
2. Presorted data-The data in the leaf nodes is already sorted by the value of the primary key. 

Disadvantages of Index:-

1. You must have a primary key on the table with a unique value.
2. You cannot have any other indexes on the data.
3. You cannot partition an index-organized table.
4. An index-organized table cannot be a part of a cluster.