 Welcome to the session on Bitmap Indices in Database, Myself Rashmi Dixie. So let us begin the session learning outcome at the end of this session students will be able to explain Bitmap Indices. So Bitmap Indices, Bitmap Indices are special type of index designed for efficient querying on a multiple keys, although each Bitmap index is built on a single key. Records in a relation are assumed to be numbered sequentially starting from say 0. Given a number n, it must be easy to retrieve record n and particularly easy if the records are of fixed size. So in its simplest form, a Bitmap index on the attribute a in the relation r consists of one Bitmap for each value of that a can take. So applicable on attributes that take on a relatively small number of distinct values. So for example, consider gender, so gender, gender either male or female, country, state. You can also use Bitmap indexing on income level, income broken up into small number of levels such as 0 to 9,009 means 9,900, 10,000 to 1,9,999 means etcetera. We can apply Bitmap indexing when we can broke a income into smaller number of levels. Now what is a structure of Bitmap index? A Bitmap is simply an array of bits, array of bits in its simplest form. A Bitmap index on an attribute has a Bitmap for each value of the attribute. Bitmap has as many bits as record. So how many records are there in a particular relation that much Bitmap bits. And in a Bitmap for value v, the bit for a record is 1, if the record has the value v for the attribute add is 0 otherwise remember this sorry. So we will take one example on Bitmap indexes. Consider a relation in structure sorry instructor info with attribute id, gender and income level. Now from 0 to n the record number is given. Now how to create a Bitmap, so Bitmap for gender. So one thing what is our one characteristic of a Bitmap? So it contain number of bits as many as number of record. So 5 record, 5 bits for male, female, 2 Bitmap, 5 record, 5 bit and 1 where male is there in the gender attribute. So 1, 0, 0, 1, 0, so this is a Bitmap for male, for female 0, 1, 1, 0, 1. So Bitmap index on attribute gender in the relation instructor info consist of 1 Bitmap for each value of that particular gender means attribute gender can take. So male and female 2 Bitmap, so male will contain 1 where the value of means where the male is there in the gender attribute. Similarly Bitmap for income level L1, L2, L3, L4, L5 so depending upon level is broken into how many levels, income levels that much Bitmap we are going to create. So where is L1, 1, 0, 1, 0, 0 same for L2, L3, L4, L5. So what are the different operations are possible with Bitmap? So Bitmap indices are useful for the queries on a multiple attributes. It is not particularly useful for a single attribute queries and queries are answered using Bitmap operations we can perform intersection, union or complementation that is AND or NOT operation. So how the operation takes place? So each operation takes 2 Bitmap of the same size and applies the operation on the corresponding bit to get the Bitmap result. For example 1, 0, 0, 1, 1, 0 AND operation OR operation NOT operation. So whatever the AND happen with the AND operation that is a result means 1, 0, 0, 1, 1, 0 AND 1, 1, 0, 0, 1, 1 the AND 1, triple 0, 1, 0 same for OR AND NOT. Now if the query mail gender mail with income level L1. So gender mail, so Bitmap for gender mail 1, 0, 0, 1, 0 AND with income level L1. So 1, 0, 1, 0, 0. So if we fire a query, find a record with gender mail or mail with income level L1 we are going to perform AND operation on 2 Bitmap of mail and income level L1. So 1, then 4, 0 means record number 1 with it will return you ID 7, 6, 7, 6, 6 means gender mail with income level L1. So can then retrieve required tuples and counting numbers of matching tuple is even faster. So what are the characteristic of Bitmap indices? Bitmap indices generally very small compared with relation size. For example if record is of 100 bytes space for a single Bitmap is 1 by 1800 of the space used by relation look at it is very small as compared to relation size. If the number of distinct attribute values is 8 Bitmap is only 1% of the relation size only 1% and deletion now one care should be taken deletion needs to be handled properly. Existence Bitmap to note if there is a valid record at record location and needed for complementation means not attribute with particular value there not a Bitmap A with the value and Existence Bitmap should keep Bitmap for all values even null values to correctly handle sequential null semantics for not attribute with a particular value intersect above result with not Bitmap with attribute with null value. So remember one thing should keep Bitmap for all values with null value. Now all of a student takes some time to answer this particular query. Now what is this query or what is this question? Instructor relation is given with 4 Bitmap S1, S2, S3, S4 now identify this S1, S2, S3, S4 are represent which Bitmap? So 4 options S1 is a bitmap index for salary below 40,000, S2 is a bitmap index for the salary 50,000 to below 60,000 means between 50,000 and 60,000, S3 bitmap index for salary 16,000 to below 70,000 and S4 is a bitmap index for salary 70,000 and above. Now S1, S2, S3, S4 so number of bitmap bits equal to number of record. So we will check or you will check one by one and try to give me the answer ok. So check out with your answer S1 is a bitmap index for salary below 40,000 am I correct? Yes, so only 40,000 value set here 1, so correct and 4th one S4 is a bitmap index for salary 70,000 and above ok 1, 1, 1, 1, 1, 1, 1, 1, 1 correct. So B and C, S2 is a bitmap index for the salary 50,000 to below 60,000 no all are 0, there is a salary bitmap index for salary 50,000 are below this must be 1, but this is 0, so this is wrong and S3 is a bitmap index for salary 60,000 to below 70,000 this is also wrong ok. So how to create index in SQL? So create index with index name on a relation with attribute list on which you are performing or using as a index. So for example create index the name of index is B index on branch with attribute branch name, use create unique index to indirectly specify and enforce the condition that the search key is a candidate key ok and not really required if SQL unique integrity constraint is supported to drop an index drop index index name, most database system allow specification of type of index and clustering. Now how to create a bitmap index? Create bitmap index, index name on relation name ok. So example student ID name address age gender and semester. So create bitmap index IDX gender that is the name of index on student with attribute on which attribute you are performing or you are creating bitmap index. Same create bitmap index IDX semester on student with attribute list. So this is a reference, thank you.