Index and Types of INDEX

INDEX AND TYPES OF INDEX

Indexes are optional structures associated with tables and clusters that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.

Oracle Database provides several indexing schemes that provide complementary performance functionality. These are:

B-tree indexes:

In computer science, a B-tree is a tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic amortized time. The B-tree is a generalization of a binary search tree in that more than two paths diverge from a single node. Unlike self-balancing binary search trees, the B-tree is optimized for systems that read and write large blocks of data. It is commonly used in databases and file systems.

B-tree cluster indexes: The rows in data pages of clustered index are ordered on the value of the clustered index keys. The leaf nodes contains the data pages of the table, root and branch nodes contain actual index pages and index rows.

Each index row contains a key value and a pointer to branch level page or to data row in the leaf node of the index. New rows are inserted to fit the ordering sequence of index keys among existing rows. When you create a primary key in sql server, it will create clustered index by default. The structure of clustered index makes them faster than non clustered index.

Bitmap indexes: Bitmap indexes have traditionally been considered to work well for data such as gender, which has a small number of distinct values, for example male and female, but many occurrences of those values. This would happen if, for example, you had gender data for each resident in a city. Bitmap indexes have a significant space and performance advantage over other structures for such data. However, some researchers argue that Bitmap indexes are also useful for unique valued data which is not updated frequently. Bitmap indexes use bit arrays (commonly called bitmaps) and answer queries by performing bitwise logical operations on these bitmaps.

Bitmap indexes are also useful in the data warehousing applications for joining a large fact table to smaller dimension tables such as those arranged in a star schema. In other scenarios, a B-tree index would be more appropriate.

Hash cluster indexes: defined specifically for a hash cluster

Global and local indexes: relate to partitioned tables and indexes

Reverse key indexes: most useful for Oracle Real Application Clusters applications

Function-based indexes: contain the precomputed value of a function/expression

Domain indexes: specific to an application or cartridge.

Indexes are logically and physically independent of the data in the associated table. Being independent structures, they require storage space. You can create or drop an index without affecting the base tables, database applications, or other indexes. The database automatically maintains indexes when you insert, update, and delete rows of the associated table. If you drop an index, all applications continue to work. However, access to previously indexed data might be slower.