1 / 28

Bitmap & Index

BITMAP INDICES Bogazici University Computer Engineering Department CmpE 422 Midterm Presentation Müge Örücü. Bitmap & Index. Bitmap ( also known as bitset or bit array ) is an array data structure that compactly stores individual bits .

emele
Download Presentation

Bitmap & Index

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. BITMAPINDICESBogazici UniversityComputer Engineering DepartmentCmpE 422 Midterm PresentationMüge Örücü

  2. Bitmap & Index • Bitmap (alsoknown as bitsetor bit array) is an array data structure that compactly stores individual bits. • A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and decreased storage space.

  3. Bitmap Indices • 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.

  4. Logical Operations • Bitmaps can be combined using the logical operations AND, OR , NOT.

  5. BitwiseLogical operations • Bitmap indices use bit arrays (commonly called bitmaps) and answer queries by performing bitwise logical operations on these bitmaps. • OR can be used to set a bit to one: 11101010 OR 00000100 = 11101110 • AND can be used to set a bit to zero: 11101010 AND 11111101 = 11101000

  6. Bitmap Indices • Each bit in the bitmap corresponds to a possible rowId, and if the bit is set, it means that the row with the corresponding rowId contains the key value. • A mapping function converts the bit position to an actual rowId, so that the bitmap index provides the same functionality as a regular index. If the number of different key values is small, bitmap indices save space. • Bitmap indices are most effective for queries that contain multiple conditions in the WHERE clause. Rows that satisfy some, but not all, conditions are filtered out before the table itself is accessed. This improves response time, often dramatically.

  7. Bitmap Indices • Introduced in Oracle 7.3

  8. EfficientUse of Bitmap Indices • For columns with very few unique values • Columns that have low cardinality are good candidates • Tables that have no or little insert/update are good candidates • For example, you had gender data for each resident in a city. Bitmap indiceshave a significant space and performance advantage over other structures for such data.

  9. Binning • For high-cardinality columns, it is useful to bin the values

  10. Creating Bitmap Indices (in Oracle) • Stream of bits: each bit relates to a column value in a single row of table • create bitmap index city_region on city (region);

  11. Bitmap Indices (in Oracle) CREATE TABLE City(Name varchar2(100) ,Regionvarchar2(100) , RowNUMBER ); CREATE BITMAP INDEX city_regionON City(Region); SELECT Name FROM CityWHERE Region=‘North’

  12. Bitmap Indices in Data Warehouses • Bitmap indices are widely used in data warehousing environments. The environments typically have large amounts of data and ad hoc queries. For such applications, bitmap indexing provides: • Reduced response time for large classes of ad hoc queries • Reduced storage requirements compared to other indexing techniques • Dramatic performance gains even on hardware with a relatively small number of CPUs or a small amount of memory • Efficient maintenance during parallel DML and loads

  13. Ad hoc queries • Ad hoc queries are those that are not already defined that are not needed on a regular basis, so they're not included in the typical set of reports or queries provided with the software. • They're typically queries that the user can create on their own outside the scope of the delivered software.

  14. Indexing Null Values • Bitmap indices index nulls, whereas all other index types do not. • Consider, for example, a table with STATE and PARTY columns, on which you want to perform the following query: SELECT COUNT(*) FROM people WHERE state='CA' AND party !='D' ;

  15. Indexing Null Values (cntd ) • Indexing nulls enables a bitmap minus plan where bitmaps for party equal to D and NULL are subtracted from state bitmaps equal to CA. The EXPLAIN PLAN output looks like the following: SELECT STATEMENT SORT AGGREGATE BITMAP CONVERSION COUNT BITMAP MINUS BITMAP MINUS BITMAP INDEX SINGLE VALUE STATE_BM BITMAP INDEX SINGLE VALUE PARTY_BM BITMAP INDEX SINGLE VALUE PARTY_BM • If a NOT NULL constraint exists on party, then the second minus operation (where party is null) is left out, because it is not needed.

  16. Multicolumn Bitmap Indices • Bitmap indices can be defined for more than one column CREATE BITMAP INDEX i1 ON t1 (c1,c2); • Can be used for queries such as SELECT * FROM t1 WHERE c1 = 0;SELECT * FROM t1 WHERE c1 = 0 AND c2 = 0; • Consider creating two indices and allowing Oracle to perform join dynamically CREATE BITMAP INDEX i1 ON t1 (c1);CREATE BITMAP INDEX i2 ON t1 (c2);

  17. Bitmap index merge • Oracle can provide sub-second response time when working against multiple low-cardinality columns.

  18. Bitmap index merge(Cont’d) •  assume there is a motor vehicle database with numerous low-cardinality columns such as car_color, car_make, car_model, and car_year • SELECTlicense_plat_nbrFROM   vehicleWHERE   color = ‘blue’AND   make = ‘toyota’AND   year = 1981; • Oracle uses a specialized optimizer method called a bitmapped index merge to service this query. In a bitmapped index merge, each Row-ID, or RID, list is built independently by using the bitmaps, and a special merge routine is used in order to compare the RID lists and find the intersecting values.

  19. Bitmap Join Indexes • One can create a bitmap join index, which is a bitmap index for the join of two or more tables. A bitmap join index is a space efficient way of reducing the volume of data that must be joined by performing restrictions in advance. For each value in a column of a table, a bitmap join index stores the rowIds of corresponding rows in one or more other tables. • Bitmap join indexes are much more efficient in storage than materialized join views, an alternative for materializing joins in advance. This is because the materialized join views do not compress the rowIds of the fact tables.

  20. Bitmap JoinIndexexample • You can create a bitmap join index on more than one column, customers(gender, marital_status): CREATE BITMAP INDEX sales_cust_gender_ms_bjix ON sales(customers.cust_gender, customers.cust_marital_status) FROM sales, customers WHERE sales.cust_id = customers.cust_id LOCAL NOLOGGING;

  21. Bitmap Join Index Restrictions • Only one table can be updated concurrently by different transactions when using the bitmap join index. • No table can appear twice in the join. • You cannot create a bitmap join index on an index-organized table or a temporary table. • The columns in the index must all be columns of the dimension tables. • The dimension table join columns must be either primary key columns or have unique constraints. • If a dimension table has composite primary key, each column in the primary key must be part of the join.

  22. Advantage of Bitmap Indices • 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 indices together for fast access to the underlying table. • Compressed indices, like bitmap indices, 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 for access - an uncompressed structure imposes a lower CPU load but requires more bandwidth to read in a short time.

  23. AboutBitmap Indices • When a bitmap index entry is locked, the entire range of rowIds is locked. The number of rowIds in this range affects concurrency. As the number of rowIds increases in a bitmap segment, concurrency decreases. • Locking issues do notaffect query performance. As with other types of indices, updating bitmap indices is a costly operation. Nonetheless, for bulk inserts and updates where many rows are inserted or many updates are made in a single statement, performance with bitmap indices can be better than with regular B-tree indices.

  24. Not touseBitmap Indices • Bitmap indices benefit data warehousing applications, but they are not appropriate for OLTP(Online transactionprocessing) applications with a heavy load of concurrent INSERTs, UPDATEs, and DELETEs.

  25. Not to use Bitmap Indices( Deadlocks ) • Bitmap indexes are not appropriate for tables that have lots of single row DML operations (inserts) and especially concurrent single row DML operations. Deadlock situations are the result of concurrent inserts as the following example shows: Open two windows, one for Session 1 and one for Session 2

  26. Disadvantage of Bitmap Indices • The reason for confining bitmap indices 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. In addition, the concurrency for modifications on bitmap indices is dreadful.

  27. History • First introduced by Professor Israel Spiegler and RafiMaayan in theirreaserch "Storage and Retrieval Considerations of Binary Data Bases", published on 1985 • The first commercial database product to implement a bitmap index is Computer Corporation of America's Model 204 in 1987.

  28. References • http://en.wikipedia.org/wiki/Bitmap_index • http://download.oracle.com/docs/cd/B10500_01/server.920/a96520/indexes.htm#97322 • http://www.stanford.edu/dept/itss/docs/oracle/9i/server.920/a96533/data_acc.htm#8131 • Understanding Bitmap Indexes (http://www.dbazine.com)

More Related