Bitmap index is one of the index types that is supported by oracle. Unlike B*tree index, this is very compressed index. It means if I create a bitmap index on a column, then the generated index table will be smaller when compared the index table which is generated by the binary index on the same column. The reason is, in bitmap index, ROWIDs won’t be stored in the index table, instead index values will be mapped against bitmaps. Bitmap is nothing but it stores either of this values, 1(Match) or 0(No Match). For each distinct values in the column, a separate index record will be created.
Unlike b*tree index, bitmap index can accept NULL and it creates separate index entry for this. Bitmap index will work better if number of distinct values are relatively less when compared to the total number of records. Why is it referred as “relatively less”? this index will work fine if table of 1,000,000,000 records contains 5000 distinct values and if table of 1,000 records contains 5 distinct values.
This bitmap index can be created on a single column or more than one column. More than one bitmap index can be created for the same data table.
HOW TO VERIFY:
How to verify whether the created index is bitmap and this bitmap index is referred by the sql query. If query uses, then you will find similar execution plan like this,
-------------------------------------------------------------------------------------------------------- |
| Id | Operation | Name | Rows | |
-------------------------------------------------------------------------------------------------------- |
| 0 | SELECT STATEMENT | | | |
| 1 | TABLE ACCESS (BY INDEX ROWID) | EMP | | |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | 3 | BITMAP INDEX SINGLE VALUE | DEPT_INDX | | |
In the explain plan, whenever it displays the bitmap index, it displays the keyword (BITMAP CONVERSION TO ROWIDS).
LITTLE-KNOWN FACTS TO BE REMEMBERED:
· Whenever oracle accesses a table, it refers a single index to get the ROWIDs. This is applicable only to the b*tree index but with the bitmap index, if a table contains multiple bitmap indexes and each index is created in different columns, and if these columns are referred in the WHERE clause, then Oracle refers all the bitmap indexes, get the list of ROWIDs and then apply logic operators (AND, OR, NOT) whichever mentioned in the query.
· Bitmap index leads to the contention issue and possibilities of blocking another session is very high. (It is explained in EXAMPLE-2)
ADVANTAGE:
· Query retrieval will be fast if the column (on which this non0unique index is created) contains NULL values and query searches for NULL values
· The advantages of them are that they have a highly compressed structure, making them fast to read and their structure makes it possible for the system to combine multiple indexes together for fast access to the underlying table.
· Compressed indexes, like bitmap indexes, represent a trade-off between CPU usage and disk space usage. A compressed structure is faster to read from disk but takes additional CPU cycles to decompress(to convert bitmaps into actual ROWIDs) for access. An uncompressed structure imposes a lower CPU load but requires more bandwidth to read in a short time.
DISADVANTAGE:
· The reason for confining bitmap indexes to data warehouses is that the overhead on maintaining them is enormous. A modification to a bitmap index requires a great deal more work on behalf of the system than a modification to a b-tree index.
· The concurrency for modifications on bitmap indexes is dreadful.
· Bitmap indexes are not meant for the tables which are continuously being affected by DML transaction.
EXAMPLE-1:
Create an employee table and inserts 10 records. The table will look like this,
DATA TABLE:
ROWID | empid | column2 | column3 | Deptid | column4 | column5 |
AAAAA1 | 1 | .. | .. | ECE | .. | .. |
AAAAA2 | 2 | .. | .. | IT | .. | .. |
AAAAA3 | 3 | .. | .. | ECE | .. | .. |
AAAAA4 | 4 | .. | .. | .. | .. | |
AAAAA5 | 5 | .. | .. | IT | .. | .. |
AAAAA6 | 6 | .. | .. | .. | .. | |
AAAAA7 | 7 | .. | .. | ECE | .. | .. |
AAAAA8 | 8 | .. | .. | IT | .. | .. |
AAAAA9 | 9 | .. | .. | ECE | .. | .. |
AAAAA10 | 10 | .. | .. | IT | .. | .. |
Fire this query against this table where the requirement is to display the all the attributes of an employee who are yet to be assigned with a department.
Select * from emp where deptid is null;
Here, we are yet to create the bitmap index on deptid column. Now, to execute this query, Oracle will take 10 seconds (assume oracle takes 1 second for single data table record search).
Why it is taking 10 seconds? Reason is, Oracle follows the sequential search when it looks for the data in the data table. Here, we are looking for the deptid, NULL. Since oracle is searching in the data table, it can’t directly go to 4th and 6th records. Oracle starts from first record and traverse thro all the records till it reaches the last one. So in b/w, whichever table record has this deptid would be displayed in the output. Explain plan will look like this,
-------------------------------------------------------------------------------------------------------- |
| Id | Operation | Name | Rows | |
-------------------------------------------------------------------------------------------------------- |
| 0 | SELECT STATEMENT | | 2 | |
| 1 | TABLE ACCESS FULL | EMP | 10 | |
Even if a btree non-unique index was created on this deptid column, it would go for a table acess full since btree index can’t understand NULL. Problem here is, oracle has unnecessarily referred the unwanted 8 table records which are not meant to be referred for this sql. In order to overcome this issue, we have to create the bitmap index on deptid column.
Why is deptid column the ideal candidate to impose bitmap index? Because each department can have single employee or more than one employee and there will be a case where employee is yet to assigned to a particular department (In this case, NULL would be stored against this type of employees). Since query is searching for NULL values, bitmap index becomes the ideal solution.
Create a bitmap index on this table for deptid column. (create bitmap index dept_id_indx on emp(deptid)).
Index table will logically look like this,
BITMAP INDEX TABLE:
INDEX | BITMAP |
ECE | 1010001010 |
IT | 0100100101 |
NULL | 0001010000 |
First column (INDEX) : it stores all the distinct values of deptid column in the ascending order along with NULL ( only if anyone of the table records has NULL).
Second column (BITMAP) : it stores 1 or 0. if a table record is matching with the respective index value, then 1 will be stored else 0 will be stored (each index value has the bitmaps for all the table records)
After creating this index table, fire the same query again,
Select * from emp where deptid is null;
When oracle executes this sql, first it looks for any index which has already been created on this “deptid” column. It comes to know that the index, “dept_id_indx” has already been created on “deptid” column. So, oracle refers the index table first before hitting the actual data table. Since we are looking for the deptid: NULL, oracle hits index table first and goes to the respective index value. Then it checks only for the value, 1 and it is intelligent enough( but it is still overhead) to convert the matching bitmaps into the actual ROWID.
After the getting the required ROWIDs (AAAAA4,AAAAA6), oracle directly refers the 4th and 6th records of the data table since it knows the exact location (ROWID of 4th record is AAAAA4 and ROWID of th record is AAAAA6). With this, it doesn’t refer the remaining 8 table records. Now, explain table will look like this,
-------------------------------------------------------------------------------------------------------- |
| Id | Operation | Name | Rows | |
-------------------------------------------------------------------------------------------------------- |
| 0 | SELECT STATEMENT | | 2 | |
| 1 | TABLE ACCESS (BY INDEX ROWID) | EMP | 2 | |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | 3 | BITMAP INDEX SINGLE VALUE | DEPT_INDX | | |
If you watch the explain plan closely, any values won’t be displayed for “Rows” column against bitmap index row. It tells that bitmap index doesn’t store ROWID and only in the next step(BITMAP CONVERSION TO ROWIDS) bitmaps are converted to actual ROWIDs. Since this index table is used, oracle will take only 3 seconds (assume 1 second to get the ROWIDs (AAAAA4,AAAAA6) from 3rd index record since it follows binary search + 2 seconds to retrieve the actual table data records since we know the exact ROWIDs which are retrieved in the previous step)
EXAMPLE-2:
Create an employee table, insert 10 records and then create bitmap index on deptid column (as mentioned in EXAMPLE-1)
Now, create two sessions,
· in the first session, inserts a new employee(whose empid is 11) for the deptid, ECE
· in the second session, inserts another new employee(whose empid is 12) for the deptid, ECE
session 1 | session 2 |
step1: insert into emp values (11,……,'ECE',………); | |
step2: insert into emp values (12,……,'ECE',………); <ß--------session2 starts to wait----------à> | |
(Here, session1 blocks the session2 though both the sessions are trying to insert different employees. As everyone aware, lock will be applied before inserting the records into any tables (data table or index table). Here the culprit is bitmap index table. Reason is, when session1 executes insert stmt(empid:11), it locks the corresponding index record (ECE) in the index table. When session2 tries to insert another employee(empid:12) for the same deptid (ECE), it comes to know that the corresponding index record (ECE) is being locked by session1. Due to this contention issue, session2 has to wait till session1 releases the lock. |
This is the reason, tables with the bitmap indexes are not meant for OLTP systems where DML activity is very high because it leads to this contention/blocking issues. Bitmap indexes are meant for OLAP and data-warehousing applications alone.
Comments
Post a Comment