Alternative: Bitmap Indexing

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