An index in database is a data structure that improves the speed of retrieval operation at the cost of some extra memory.Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed.
Note:->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.
Index Architecture
Clustered Indexing:-In Clustered indexing the data block is altered into a certain distinct order to match the index, resulting in the row data being stored in order. Clustered indexes associates a primary key(or any column on which index is to created) with the physical location in which a record is stored. 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.
Non Clustered Indexing :-In non clustered indexing the data is present in arbitrary order regardless of the value of the the index column . But the indexing keys are always stored in sorted order with the address of the data being present in the record .
Clustered indexing contain two things :
1.The primary key(or column ) of the record.
2.The physical address of the record.
There can be more than one non-clustered index on a database table.
Types of Indexes :
Bitmap Index:-A bitmap index is a special kind of index that stores the bulk of its data as bit arrays (bitmaps) and answers most queries by performing bitwise logical operations on these bitmaps.
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
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.
Which data structure is generally used for indexing ?
B-Tree is generally used for indexing because it is a balanced tree and searching , deletion and all the operation are done in proper way in B -Tree.
Note:->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.
Index Architecture
Clustered Indexing:-In Clustered indexing the data block is altered into a certain distinct order to match the index, resulting in the row data being stored in order. Clustered indexes associates a primary key(or any column on which index is to created) with the physical location in which a record is stored. 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.
Non Clustered Indexing :-In non clustered indexing the data is present in arbitrary order regardless of the value of the the index column . But the indexing keys are always stored in sorted order with the address of the data being present in the record .
Clustered indexing contain two things :
1.The primary key(or column ) of the record.
2.The physical address of the record.
There can be more than one non-clustered index on a database table.
Types of Indexes :
Bitmap Index:-A bitmap index is a special kind of index that stores the bulk of its data as bit arrays (bitmaps) and answers most queries by performing bitwise logical operations on these bitmaps.
| A bit array (also known as bitmap, bitset, bit string, or bit vector) is an array data structure that compactly stores bits. It can be used to implement a simple set data structure.A bit array is effective at exploiting bit-level parallelism in hardware to perform operations quickly. |
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
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.
Which data structure is generally used for indexing ?
B-Tree is generally used for indexing because it is a balanced tree and searching , deletion and all the operation are done in proper way in B -Tree.
No comments:
Post a Comment