EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Database Management Tutorial Bitmap Indexing
Secondary Sidebar
Database Management Tutorial
  • DataBase Management
    • Text Data Mining
    • Roles of Database Management System in Industry
    • SQL Server Database Management Tools
    • Databricks CLI
    • Database administrator skills
    • Database Management Systems Advantages
    • Database Testing Interview Questions
    • Netezza Database
    • Data Administrator
    • Database Administrator
    • Data manipulation
    • Database Management Software
    • DataStage
    • Types of Database Models
    • Types of Database
    • Hierarchical Database Model
    • Relational Database
    • Relational Database Advantages
    • Operational Database
    • What is RDBMS?
    • Data Masking Tools
    • Database Security
    • Data Replication
    • Bitmap Indexing
    • Second Normal Form
    • Third Normal Form
    • Fourth Normal Form
    • Data Definition Language
    • Data Manipulation Language
    • Data Control Language
    • Transaction Control Language
    • Dataset Normalization
    • jdbc connection
    • Conceptual Data Model
    • Entity-Relationship Model
    • Relational Database Model
    • Sequential File Organization
    • Teradata Create Table
    • Teradata Database
    • Centralized Database
    • Data Storage in Database
    • Thomas write Rule
    • DBA Interview Questions
    • What is JDBC?
    • jdbc hive
    • Apriori Algorithm
    • JDBC Architecture
    • JDBC Interview Questions
    • Datastage Interview Questions
    • Wildcard Characters
    • Distributed Database System
    • Multidimensional Database
  • TSQL Basic
    • TSQL
    • What is T-SQL
    • T-SQL Commands
    • T-SQL String Functions
    • TSQL Interview Questions

Related Courses

SQL Certification Course

PL/SQL Certification Course

Oracle Certification Course

Bitmap Indexing

By Asapu HarikaAsapu Harika

Bitmap Indexing

Introduction to Bitmap Indexing

The bit is the smallest unit used to represent data that has 0 or 1 as its values. It is the bit arrays used to answer queries by performing bitwise logical operations on bitmaps. Indexing refers to a data structure that organizes data based on a specific schema. This bitmap indexing technique is used in huge databases when the column has low cardinality and is frequently used in the query. Low cardinality columns refer to columns with few unique values. We shall discuss why it is needed, its uses, advantages, and disadvantages of it in today’s IT field.

It is used in Data warehousing applications with a large amount of data and complex queries but low-level transactions, for such kind of applications, it helps in reducing response time for complex queries, space reduction compared to other techniques of indexing, increase in hardware performance with less number of CPU’s and memory and effective maintenance. Low cardinality columns have several distinct values absolutely or relatively to the number of records containing data. At extreme, low cardinality can also have Boolean values i.e. true or false.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

The index provides pointers to rows in a table that contain given key value. A normal index stores list of row ids for each key parallel to the row with key-value whereas it stores each key-value replacing its list of row ids.

The need for Bitmap Indexing

Let us consider that a Public school holds Student data having columns as ‘student name’, ‘student’, ‘specialization’, ‘marks’. On the assumption that students will have to give exams only once in an academic year, hence the table will be updated once and remain static throughout the year.

As columns will be frequently used for querying to retrieve data, like a Total number of male students which needs file organization method to give quicker results. As traditional file organization method is not fast comparatively, hence comes into picture Bitmap Indexing for higher-level storage and retrieval of data.

Consider below an example of bitmap indexing which can explain Use of it,

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,112 ratings)

Consider a table with columns, employee, employeeName, gender, employmentStatus.

The gender column has only two distinct values ‘Male’ or ‘Female’, hence it is useful for bitmap indexing. On comparing the whole employee table, they have no unique value except for the gender column.

The employee table has only 5000 rows, the number of indices on the column will be equal to the number of distinct values in the column. The gender column will have 2 bitmap indices, one for male and other for female. If there is any matching value in the column for a row, that particular row bit will be ‘1’ or ‘0’. Bit value for ‘male’ index be ‘1’ and for ‘female’ be ‘0’.

employee employeeName Gender employmentStatus
101 Krishna F Permanent
102 Sandeep M Temporary
103 Karthika F Hourly based
104 Saideep M Weekly based

As we are considering to index gender column, bitmap index ‘F’ has value ‘1010’ whereas ‘M’ has ‘0101’

New_gender_values Bitmap Indices
F 1010
M 0101

Suppose we need to find details of Employee with ‘Weekly based’ employee status and is a male.

Code:

SELECT * FROM employee WHERE gender=’M’ and employeeStatus=’Weekly based’;

Hence, the Bitmap Index for Male: 0101, similarly index for Weekly based is 0001. In DBMS, logical AND operation is performed to find the output of the query.

0101 Bitmap index for gender ‘Male’

+

0001 Bitmap index for employment Status as ‘Weekly based’

=0001 The result here represents that the last column has to be retrieved.

Lesser the unique values in the column, lesser is the cardinality. Hence Cardinality is directly proportional to a count of unique values. Even with less cardinality, these columns are used for querying often. A bitmap indexing can also be built on columns with higher cardinality, consider a table with million rows having 50000 rows with distinct values. B-tree indexing can be used for data with high cardinality.

High cardinality bitmap indexing would not work as there has to be a new data structure record for each unique value in the column, as the number of unique value increases, the index will take up more space leading to inefficiency.

Advantages and Disadvantages of Bitmap Indexing

Below are the advantages and disadvantages:

Advantages

  • It helps in faster retrieval of data when fewer cardinality columns and are used in querying frequently
  • It is efficient even if the table has a huge number of records.
  • It would be much more efficient if table columns are involved in insertion/ updating/ deletion operations
  • Multiple indices can be combined for executing a query.
  • A Bitmap join index can be created on a temporary table

Disadvantages

  • Bitmap indexing is not suitable for tables with fewer records, as DBMS forces full scan over the table for result instead of using bitmap indexing
  • Multiple insertions/ updating/ deletion operation from different users can cause deadlock
  • To perform DML operations, bitmap indexing takes time to perform the transaction and update its bitmap index.
  • A large number of records can cause overhead in maintaining it.
  • It has to be modified throughout if the user enters a new record which is time-consuming and difficult.
  • Only one table can be updated by different transactions while using the bitmap join index.
  • It can not be created with the UNIQUE attribute in the table.

Conclusion

As we have come to an end of the article, let us summarize what have we gone through. We have seen what Bitmap Indexing means and what it does. How it is performed in SQL via AND operation has been illustrated above. Need for it, its uses, advantages, and disadvantages.

Recommended Articles

This is a guide to Bitmap Indexing. Here we discuss an introduction to Bitmap Indexing, needs with explanation, advantages, and disadvantages. You can also go through our other related articles to learn more –

  1. Types of Database
  2. Indexes in Hive
  3. Database Management Systems Advantages
  4. What is Spatial Data
Popular Course in this category
All in One Data Science Bundle (360+ Courses, 50+ projects)
  360+ Online Courses |  1500+ Hours |  Verifiable Certificates |  Lifetime Access
4.7
Price

View Course

Related Courses

SQL Training Program (7 Courses, 8+ Projects)4.9
PL SQL Training (4 Courses, 2+ Projects)4.8
Oracle Training (14 Courses, 8+ Projects)4.7
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more