Library and Information Science, Computer Applications For Educated youths (LIS Cafe) an International Education website, (India’s first and World’s largest website for online study to Library and Information Science and Computer Applications through objective and subjective questions).______Knowledge & Thoughts is free at the LIS Cafe. Just bring your own learning Container. -Asheesh Kamal
Blinkie Text Generator at TextSpace.netToday's LIS Word:-Click Here___________

Blinkie Text Generator at TextSpace.netLive EPISODE-45


Blinkie Text Generator at

LIS Cafe Has 10,000 Objective Questions with Answer-Click Here....

Search This LIS Cafe- Enter here

"Share your Knowledge. It is a Way to Achieve Immortality".---Dalai Lama XIV
अपने ज्ञान को साझा करना (शेयर), यह एक तरह से अमरत्व को प्राप्त करने जैसा है- दलाई लामा XIV (Translated By-Asheesh kamal)

Tuesday, August 11, 2015

Database Index: Types and Uses

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 a database 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 select columns of data from a table that can be searched very efficiently that also includes a low-level disk block address or direct link to the complete row of data it was copied from. Some databases extend the power of indexing by letting developers create indexes on 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 indices, 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.

Index architecture/Indexing Methods
The data is present in arbitrary order, but the logical ordering is specified by the index. The data rows may be spread throughout the table regardless of the value of the indexed column or expression. The non-clustered index tree contains the index keys in sorted order, with the leaf level of the index containing the pointer to the record (page and the row number in the data page in page-organized engines; row offset in file-organized engines).

In a non-clustered index,
  • The physical order of the rows is not the same as the index order.
  • The indexed columns are typically non-primary key columns used in JOIN, WHERE, and ORDER BY clauses.
There can be more than one non-clustered index on a database table.

Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. Clustered indices can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected.
Since the physical records are in this sort order on disk, the next row item in the sequence is immediately before or after the last one, and so fewer data block reads are required. The primary feature of a clustered index is therefore the ordering of the physical data rows in accordance with the index blocks that point to them. Some databases separate the data and index blocks into separate files, others put two completely different data blocks within the same physical file(s).

When multiple databases and multiple tables are joined, it's referred to as a cluster (not to be confused with clustered index described above). The records for the tables sharing the value of a cluster key shall be stored together in the same or nearby data blocks. This may improve the joins of these tables on the cluster key, since the matching records are stored together and less I/O is required to locate them. The cluster configuration defines the data layout in the tables that are parts of the cluster. A cluster can be keyed with a B-Tree index or a hash table. The data block where the table record is stored is defined by the value of the cluster key.

Types of indexes
Bitmap index
A bitmap index is a special kind of indexing that stores the bulk of its data as bit arrays (bitmaps) and answers most queries by performing bitwise logical operations on these bitmaps. The most commonly used indexes, such as B+trees, are most efficient if the values they index do not repeat or repeat a small number of times. In contrast, the bitmap index is designed for cases where the values of a variable repeat very frequently. For example, the gender field in a customer database usually contains at most three distinct values: male, female or unknown (not recorded). For such variables, the bitmap index can have a significant performance advantage over the commonly used trees.

Dense index
A dense index in databases is a file with pairs of keys and pointers for every record in the data file. Every key in this file is associated with a particular pointer to a record in the sorted data file. In clustered indices with duplicate keys, the dense index points to the first record with that key.

Sparse index
A sparse index in databases is a file with pairs of keys and pointers for every block in the data file. Every key in this file is associated with a particular pointer to the block in the sorted data file. In clustered indices with duplicate keys, the sparse index points to the lowest search key in each block.

Reverse index
A reverse key index reverses the key value before entering it in the index. E.g., the value 24538 becomes 83542 in the index. Reversing the key value is particularly useful for indexing data such as sequence numbers, where new key values monotonically increase.

1. PostgreSQL 9.1.2 Documentation: CREATE TABLE
Overview of Clusters Oracle® Database Concepts 10g Release 1 (10.1)
2. Database Systems: The Complete Book. Hector Garcia-MolinaJeffrey D. UllmanJennifer D. Wisdom
3. Gavin Powell (2006). "Chapter 8: Building Fast-Performing Database Models". Beginning Database Design ISBN 978-0-7645-7490-0Wrox Publishing.
4.  "Clustered Index Structures". SQL Server 2005 Books Online (September 2007).
5. Daren Bieniek; Randy Dess; Mike Hotek; Javier Loria; Adam Machanic; Antonio Soto; Adolfo 5. 6. Wiernik (January 2006). "Chapter 4: Creating Indices". SQL Server 2005 Implementation and Management. Microsoft Press.

7. Covering Indexes for Query Optimization

Thanks and Regards
Asheesh Kamal

Post a Comment

Must Watch with Song

Review LIS Cafe

Blogger Tips and TricksLatest Tips For BloggersBlogger Tricks

Full form of LIS Cafe

Full form of LIS Cafe

Please Share to the Information.