Databases Reference
In-Depth Information
than 1% of the overall rows in the table, it is a feasible candidate for a bitmap index. It's better to use a
percentage rather than strictly go off of the number of valid values for a given column. For example, let's
say your table contains 1,000 rows. If you use the 1% guideline, it means if a column's valid values
number 10 or less, it would be a viable candidate as a column for a bitmap index. On a much grander
scale, if you have a one billion row table, 1% of that is 10 million rows. While some may not believe that
10 million valid values for a column is a good candidate for a bitmap index, it actually may be a viable
candidate. You need to look at that cardinality in relation to the data volume as a whole.
With bitmap indexes, nulls are handled differently than with B-tree indexes. Unlike B-tree indexes,
null values with an indexed column
are
stored within a bitmap index. In B-tree indexes, null values are
not stored, at least within single-column indexes. To clarify, nulls can be stored in composite or
multiple-column B-tree indexes as long as one of the columns contains a non-null value.
Because the bitmap index stores a value for each row in your table, it will include any null values
that exist on the indexed column. This has performance implications when issuing queries on columns
that have a bitmap index associated with that column. Since B-tree indexes may not store null values, if a
query on a column with a single-column index that contains null values is issued, it needs to either
perform a full table scan or use another existing index and then filter the null value after the row has
been retrieved. This is not the case with bitmap indexes. The index can still be used in certain
circumstances where functions such as
COUNT
are used.
See the following example of a query against the
GENDER
column of your
EMPLOYEES
table, which has
been indexed using a bitmap index. You can see from this query that the
GENDER
column has not yet been
populated for any of the rows and thus is null.
SQL> select count(*), count(gender) from employees_nulltest;
COUNT(*) COUNT(GENDER)
---------- -------------
42074112 0
When you issue a query against the
GENDER
column, the explain plan shows that even with null
values in the table an index is used.
SELECT count(*) FROM employees_nulltest
WHERE gender = 'F';
----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | BITMAP CONVERSION COUNT | |
| 3 | BITMAP INDEX SINGLE VALUE| EMPLOYEES_B9 |
--------------------------------------------------
If you index the same column on the same table with a B-tree index, the explain plan shows that the
query will perform a full table scan.
--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| EMPLOYEES_NULLTEST |
--------------------------------------------------
