Database index
A database index , or index for short (in the plural "indices" or "indices"), is an index structure in a database that is separate from the data structure and that speeds up the search and sorting according to certain fields.
An index consists of a collection of pointers (references) that define an ordering relation to one or more columns in a table. If an indexed column is used as a search criterion in a query, the database management system (DBMS) searches for the desired data records using these pointers. As a rule, B + trees are used here . 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 , an index is created with the command
CREATE INDEX Indexname ON Tabellenname ( Spaltenname(n) )
defined (no standard SQL, see below). Most of the time a single column is indexed, but compound indexes are also possible in most database systems. An index is automatically placed on columns that contain primary keys (SQL clause primary keyin the command create table).
Sorting the data according to a primary index is often insufficient, so that additional indexes are required. If an overall table of contents is now set up for these further developments, a secondary index is created .
Types of indexes
Bitmap index
The bitmap index is based on the storage of the column values in the form of bit strings. This index type is used for database-technical reasons with low selectivity and low update expectation of the column (s) to be indexed.
Clustered index
Many database management systems also allow the definition of a clustered index. This differs from a non-clustered index in that not only is the list of pointers to the data records available in sorted form, but that the DBMS also tries to physically close in memory newly inserted data records that are close together within the index put together. This can further accelerate the search for values in this column.
Functional index
A functional index (English functional index or function based index ) is understood to be a special form of an index in a database management system. In contrast to a normal index, the pure field values, for example the first name, are not included in the index, but values transformed using database functions, for example to_upper(Vorname)for conversion to uppercase letters.
Reverse index
A reverse index is an index in which the values are reversed bit-by-bit or byte-wise before being saved. When reading this index, the read values must be converted back into the correct order before they can be evaluated. As with other indices, the 'swapped' values are usually saved as a B-tree. A reverse index has the advantage that when consecutive keys are inserted, the index tree does not become unbalanced and has to be reorganized. However, it has the disadvantage that a range scan (e.g. where nr between 100 and 120 ) cannot be evaluated using the reverse index.
Partitioned index
Just as database tables can be partitioned, indexes can also be partitioned. A distinction is made whether the partitioning is based on the first column that is indexed or on another column.
If the database table to which the index refers is partitioned, the index can be partitioned according to the same criteria (local index partitioning). Some database systems e.g. B. Oracle also offer the option of partitioning an index according to other criteria (global index partitioning).
Indexes in SQL
None of the various SQL standards define commands for indexes. The commands for creating and removing indices are therefore always database-specific. However, the CREATE INDEX and DROP INDEX commands have largely prevailed.