- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Other Types of Indexes
Hash Indexes
In DBMS, it is possible to create access structures that function similar to indexes by using hashing. This secondary structure is known as a hash index and is used to access the file based on a search key that is different from the one used for the primary data file organization. Index entries in a hash index are of the form <K, Pr> or <K, P>, where K is the search key, Pr is a pointer to the record containing the key, or P is a pointer to the block containing the record for that key.
The hash index file can be organized as a dynamically expandable hash file, utilizing one of the techniques. The hash search algorithm is used on the search key K to find the corresponding record in the data file by using the pointer Pr (or P) once an index entry is found. The hashing function used to determine the bucket number for the search key K can be anything, but in practice, the sum of the digits of K modulo 10 is often used.
Figure given below depicts an example of a hash index on the Emp_id field for a sequential file ordered by Name. Emp_id is hashed using the sum of its digits modulo 10, and the resulting bucket number is used to access the bucket containing the index entry < 51024, Pr >. Once the entry is located, the pointer Pr leads to the actual record in the file.
In practical applications, there may be thousands of buckets, and the directory schemes for dynamic hashing may be applied to the bucket number. Other search structures can also be utilized as indexes.
Bitmap Indexes
A bitmap index is a data structure used to query multiple keys in a relation with a large number of rows. It involves indexing one or more columns where each value or value range in those columns is indexed. This approach is usually taken for columns that have a relatively small number of unique values. To construct a bitmap index, the records in a relation must first be assigned a unique identifier, such as a record or row ID, which can be mapped to a physical address consisting of a block number and record offset within the block.
To create a bitmap index, one or more columns are indexed, and each unique value or value range in those columns is indexed. A bitmap index is typically created for columns that contain a small number of distinct values. To build a bitmap index for a set of records in a relation. Each record is assigned a unique id, such as a record id or a row id. It can be mapped to a physical address made up of a block number and a record offset within the block.
A bitmap index is created for a specific value of a specific field, or column, in a relation and is represented as an array of bits. For a relation with n rows, a bitmap index contains n bits. The ith bit is set to 1 if the row i has the value V for column C, otherwise it is set to 0. If the column C contains m distinct values, then m bitmap indexes would be created for that column.
Figure above shows the EMPLOYEE relation with columns Emp_id, Lname, Sex, Zipcode, and Salary_grade and a bitmap index for the Sex and Zipcode columns. For example, if the bitmap for Sex = F, the bits for Row_ids 1, 3, 4, and 7 are set to 1, and the rest of the bits are set to 0. Bitmap indexes can be used for different query applications, including:
For the query C1 = V1, the corresponding bitmap for value V1 returns the Row_ids containing the rows that qualify.
For the query C1 = V1 and C2 = V2 (a multikey search request), the two corresponding bitmaps are retrieved and intersected (logically AND-ed) to yield the set of Row_ids that qualify. In general, k bit vectors can be intersected to deal with k equality conditions. Complex AND-OR conditions can also be supported using bitmap indexing.
To count the number of rows that meet the condition C1 = V1, the corresponding bit vector is scanned to count the number of entries with a value of "1." For queries with negation, such as C1 ≠ V1, the Boolean complement operation is applied to the corresponding bitmap to obtain the desired result.
In a database management system (DBMS), when records are deleted, the process of renumbering rows and shifting bits in bitmaps can be expensive. To avoid this, an existence bitmap can be used, with a 0 bit for rows that have been deleted but are still present, and a 1 bit for rows that actually exist. When a row is inserted in the relation, an entry must be made in all the bitmaps of all the columns that have a bitmap index. Large bitvectors are handled efficiently by treating them as a series of 32-bit or 64-bit vectors, using corresponding AND, OR, and NOT operators from the instruction set to deal with 32- or 64-bit input vectors in a single instruction.
Bitmaps can also be used on the leaf nodes of B+-tree indexes to point to the set of records that contain each specific value of the indexed field in the leaf node. For values that occur very frequently, a bitmap index may be stored instead of the pointers.
Function-Based Indexing
Function-based indexing is a new type of indexing introduced in Oracle DBMS and some other commercial products. The purpose of function-based indexing is to create an index based on a function. That is applied to one or more columns of a table. This function transforms the value of the column(s) into a new value that becomes the key for the index. Function-based indexing can improve query performance by enabling the use of indexes even when a function is used in the search predicate of a query.
To create a function-based index, the CREATE INDEX statement is used. The index is created on a function that is applied to one or more columns of a table.
Function-based indexes can also be used to create an index on the result of an expression involving one or more columns of a table.
Function-based indexing can also be used to define conditional uniqueness.
99 Views
