Bitmap Indexes and Bitmap Filters
Posted by decipherinfosys on May 23, 2008
While troubleshooting an issue yesterday at a client site, a question came up about bitmap indexes vs the bitmap filters. The two are not the same and it is important to understand the differences between the two. Bitmap indexes, as the name suggests, are physical structures that persist to disk and like other indexes are used for retrieval of the data whereas bitmap filters are memory resident and are used for enhancing the performance of the query at runtime. SQL Server 2005 does not have any bitmap indexes like Oracle does. You can read more on Oracle’s bitmap indexes here. SQL Server on the other hand, takes care of this internally by using bitmap filters. It does not require hints or metadata and there is no additional storage costs since there are no indexes.
When you see the execution plans in SQL Server, you will sometimes see the operator bitmap filter and it confuses people who are used to working in both Oracle and SQL Server. Bitmap filters are used only in parallel execution plans (not serial). It helps in improving the performance of the query by doing a semijoin reduction early in the query execution. Bitmap filters are typically used when the optimizer decides to do a Hash Join or a Merge Join (refer to the different join methods here). Bitmap filters are very useful in optimization of data warehouse queries. The star join query optimizations use bitmap filters which essentially means that it performs a semi-join reduction i.e. only the rows in the second table that qualify for the join in the first table are processed. SQL Server 2008 has even more improvements in this area. While in SQL Server 2005, the bitmap filter was applied to only one join, in SQL Server 2008, multiple bitmap filters are allowed over the same fact table. In addition, in SQL Server 2008, the bitmap filters can be moved and re-ordered dynamically based on the selectivity.
You can read more on bitmap filtering at the MSDN post here.