Database index

A database index, or short index ( in the plural " indexes " or " indices " ), is separate from the data structure in a database index structure that speeds up searching and sorting by specific fields.

An index consists of an array of pointers ( references ) that define an order relation on one or more columns in a table. If, in a query an indexed column used as a search criterion, the database management system (DBMS) examined the records you want using this pointer. As a rule, find the B - Trees use. Without an index, the column would have to be searched sequentially, while a search using the tree has only logarithmic complexity.

In the database language SQL is an index with the command

CREATE INDEX index_name ON table_name ( column name (s) ) defined ( not standard SQL, see below). In most cases a single column is indexed, but also composite indexes are possible in most database systems. In columns that contain primary key ( primary key SQL clause in the create table command ), an index is automatically selected.

Often, the sorting of the data is not sufficient for a primary index, so that further indices are required. If now for these additional surveying a table of contents built, creating a secondary index.

Types of indexes

Bitmap Index

The bitmap index is based on storing the column values ​​in the form of bit strings. This type of index is out of database technical reasons use in low selectivity and low update expected to be indexed column (s).

Clustered Index

Many database management systems also allow the definition of a clustered index. This differs in that not only the list of pointers present on the records in sorted order, but that the DBMS will also attempt, newly inserted records that fall within the index close together physically in the store close by a non- clustered index store together. This can include the search for values ​​of this column accelerate.

Functional Index

Under a functional index (English functional index or function based index ) refers to a special form of an index in a database management system. Unlike a normal index are not the pure field values ​​, such as the first name added to the index, but using database functions transformed values, for example to_upper ( first name ) for a conversion to uppercase.

Reverse Index

Under a reverse subscript is an index in which the values ​​are bit-wise or byte- reversed before saving. When you read this index the read values ​​must be converted into the right order again before they can be evaluated. The ' reversed ' values ​​are usually stored as B- tree as with other indices. A reverse index has the advantage that with a paste of consecutive keys of the index tree does not come in an unbalanced condition and must be reorganized. However, it has the disadvantage that a range scan (for example, where no Between 100 and 120 ) can not be evaluated through the reverse index.

Partitioned Index

Just as database tables can be partitioned even indexes can be partitioned. A distinction is whether the partitioning is oriented to the first column that is indexed, or to another column.

If the database table to which the index applies, is partitioned, then the index can be partitioned according to the same criteria (local index partitioning ). Some database systems such as Oracle also offer the ability to partition an index based on other criteria (global index partitioning ).

Indexes in SQL

None of the various SQL standards define commands for indices. The commands for creating and removing indexes are therefore always database- specific. However, the commands CREATE INDEX and DROP INDEX are enforced largely.