1 / 11

Alternative: Bitmap Indexing

Alternative: Bitmap Indexing. Imagine the following query in huge table Find customers living in London, with 2 cars and 3 children occupying a 4 bed house Index not useful – why? Too big If query changes in any way =>new index needed

sai
Download Presentation

Alternative: Bitmap Indexing

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. Alternative: Bitmap Indexing • Imagine the following query in huge table Find customers living in London, with 2 cars and 3 children occupying a 4 bed house • Index not useful – why? • Too big • If query changes in any way =>new index needed • Maintaining a set of indexes for each query would just be too costly • Use a bitmap

  2. Bitmap Indexing • Bitmap Indexes are widely used in data warehousing environments. The environments typically have large amounts of data and ad hoc queries, but a low level of concurrent DML transactions. • For such applications, bitmap indexing provides: • Reduced response time • Reduced storage requirements • 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 • Bitmap indexes are typically only a fraction of the size of the indexed data in the table.

  3. Bitmap Indexes • Special for data warehouse type DBs • Build one bitmap for each relevant parameter • Combine bitmaps using the “AND” SQL keyword • Also possible to use “not”ing of Bitmap

  4. Key Points to Remember • What is the key advantage of a bitmap index? • What situation does it best suit ? • Bitmaps can also be packed by Oracle compression features • But size is unpredictable – why?

  5. Bitmap Example • Table with 1,000,000 rows • Bitmap on one column that can contain one of 8 different values (e.g. city names) • Data is such that all same city together 125,000 times • Write the bitmap • Imagine what compression can be achieved • Data is such that cities are in random order, but same number of each • Same questions

  6. Solution First scenario • Bitmap for first city: 125,000 ones and 875,000 zeros [trimmed off] • Size ~ 125,000 bits or approx 18Kbytes • Full bitmap = 156 Kbytes Second scenario: • Bitmap for first city sequences of 1’s and 7 zeros, repeated 125,000 times • Size ~ 1,000,000 bits or approx 140 Kbytes • Full bitmap = 1.12 Mbytes • But B-Tree index for such data would be around 12MB

  7. Bitmap Diagram A 1-bit map example (e.g. scenario 1)

  8. Conclusion • Bitmap Indexes work best when combined • They are very quick to build • Up to a million rows for 10 seconds • Work best when limited number of values + when high repetitions • Best way to deal with huge volumes => make drastic selection of interesting rows before reading the table • Warning: one entry in a Bitmap = hundreds of records = > locking can be crazy (OLTP systems) • For datawarehouse type applications (no contention)

  9. What Oracle Says about It • Use index for queries with low hit ratio or when queries access < 2 - 4% of data • Index maintenance is costly so index “just in case” is silly • Must analyse the type of data when deciding what kind of index • Do NOT use columns with loads of changes in an index • Use indexed fields in “where” statement • Can also write queries with NO_INDEX

  10. Administering Indexes • Indexes degrade over time • Should stabilise around 75% efficiency, but don’t • Run stats: Analyse index NAME validate structure Analyse index NAME compute statistics Analyse index NAME estimate statistics sample 1 percent

  11. Bitmap Join Indexes • Review for next week. • Link on college webpage

More Related