Usage and Benefits of Database Indexes
– Database indexes improve the speed of data retrieval operations on a table.
– Indexes allow for quick data location without searching every row.
– Indexes can be created using one or more columns of a table.
– They enable rapid random lookups and efficient access of ordered records.
– Indexes can be created on transformed column values or user-defined functions.
– Indexing technology in database software enables sub-linear time lookup.
– Lookup performance can be improved by using different data structures for indexing.
– Many index designs exhibit logarithmic or O(log(N)) lookup performance.
– Some applications can achieve flat or O(1) performance with indexes.
– Indexes are used to improve performance for databases with many objects.
Database Constraints and Indexing
– Indexes are used to enforce database constraints like UNIQUE, PRIMARY KEY, and FOREIGN KEY.
– UNIQUE constraints can be implemented using indexes.
– PRIMARY KEY columns are usually indexed by default in database systems.
– FOREIGN KEY constraints often require indexing of both referencing and referenced columns.
– EXCLUSION constraints can be enforced using indexes with fast searching capabilities.
Index Architecture and Methods
– Non-clustered indexes store data in arbitrary order but specify logical ordering.
– Non-clustered indexes contain index keys in sorted order with pointers to the records.
– Non-clustered indexes are used for non-primary key columns in JOIN, WHERE, and ORDER BY clauses.
– Multiple non-clustered indexes can be created on a table.
– Clustered indexes alter the data block to match the index, storing row data in order.
Column Order and Index Performance
– The order of columns in an index definition is important.
– Retrieving row identifiers using only the first indexed column is possible.
– Retrieving row identifiers using the second or greater indexed columns may not be efficient.
– The order of search columns should be considered when creating indexes.
– Index performance can be improved by creating indexes in the order of search columns.
Types of Indexes and Implementations
– Bitmap index: Stores data as bit arrays and performs bitwise logical operations.
– Dense index: Contains pairs of keys and pointers for every record in the data file.
– Sparse index: Contains pairs of keys and pointers for every block in the data file.
– Reverse index: Reverses the key value before entering it in the index.
– Primary index: Contains the key fields of the table and a pointer to the non-key fields.
– Balanced trees: Popular index structure used for efficient searching.
– B+ trees: Another commonly used index structure for efficient retrieval.
– Hashes: Most commonly used index in data management for columns with unique values.
– Microsoft SQL Server: Uses a clustered index where the leaf node corresponds to the actual data.
– Single clustered index: Each relation can have a single clustered index and many unclustered indices.
Note: The subtopic “Covering index” does not have enough information to form a comprehensive group. The subtopic “Index concurrency control” can be included in Group 2 or Group 5, depending on the focus of the organization. The subtopic “Standardization” can be included as a separate group.
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time said table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.
An index is a copy of selected columns of data, from a table, that is designed to enable very efficient search. An index normally includes a "key" or direct link to the original row of data from which it was copied, to allow the complete row to be retrieved efficiently. Some databases extend the power of indexing by letting developers create indexes on column values that have been transformed by functions or expressions. For example, an index could be created on upper(last_name), which would only store the upper-case versions of the last_name field in the index. Another option sometimes supported is the use of partial index, where index entries are created only for those records that satisfy some conditional expression. A further aspect of flexibility is to permit indexing on user-defined functions, as well as expressions formed from an assortment of built-in functions.
