Index (database)

From Wikipedia, the free encyclopedia.

This is a special case of Index that relates to Relational database management system (RDBMS) tables.

An index can be defined on one or more columns in a table (these columns are referred to as the indexed columns). The index maps each set of indexed column values to the set of unique identifiers for the rows that have those column values. This structure provides a quick way to find the rows within a table using the indexed column(s).

Besides allowing indexes to be created on one or more table columns, some RDBMSes allow indexes to be created on functions or expressions. For example, an index could be created on "upper(last_name)||', '||upper(first_name)".

Uniqueness

Indexes can be defined as unique or non-unique. A unique index does not allow any combination of column values to map to more than one row.

Index architecture

There are two kind of architecture for indexes which are clustered and non-clustered.

Both types of index use a balanced tree (b-tree) data structure where the index is arranged in a tree with 2 types of pages.

At the top of the tree is the index set of pages. These act as an index to the index.

At the lowest (or leaf) level is the sequence set of pages.

Non clustered indexes: the leaf level contains one ore more columns together with pointers to the data pages containing rows with the given index values.

Clustered indexes: the leaf level is the actual data page. Data is physically stored on a data page in ascending order. The order of values in the index pages is also ascending.

Column order

The order that columns are listed in the index definition is important. It is possible to retrieve a set of row identifiers using the only the first indexed columns. However, it is not possible or efficient (on most RDBMSs) to retrieve the set of row identifiers using the only the second or greater indexed column.

For example, imagine a phone book that is organized by city first, then by last name, and then by first name. If given the city, you can easily extract the list of all phone numbers for that city. However, in this phone book it would be very tedious to find all the phone numbers for a given last name. You would have to look within each cities section for the entries with that last name. Some databases can do this, others just won�t use the index.

de:Datenbankindex

nl:Index (database) ru:Индексация в поисковых системах