Bitmap indexes not being used unless hints forced

I'm experimenting with Oracle's bitmap indexes vs. B-tree indexes, and the results of my investigations have me nonplussed.

I have a table with a large amount of repetitive data of medium-to-high cardinality, like so:

        A          A          A
        A          A          B
        A          B          B
        B          C          A
        B          C          B
        C          C          D
        (etc.)

There are more than three columns in this table, but you get the picture. So far, even though the cardinality of for some column can be high, bitmap indexes have proven themselves very fast -- often twice as fast, and up to 10 times as fast for some operations, compared to equivalent B-tree indexes.

And yet, Oracle's optimizer will never use these indexes unless forced to using an index hint, even though bitmaps are *consistently* faster than the equivalent B-tree index or full table scans.

Am I doomed forever to use hints because Oracle's optimizer is dumb? Or is the dumbness on my part? Did I forget anything? Could I have accidentally disabled bitmaps somehow?

In the example below, the hinted query runs about 2.23 times as fast as the non-hinted version. The gap widens considerably with more complex queries -- in favour of bitmaps -- where the multiple bitmapped index can be merged.

From SQL*Plus:

SQL> create table foo (a varchar(255), b varchar(255));
...

SQL> create bitmap index foo_a_ix on foo(a asc);
...

SQL> create bitmap index foo_b_ix on foo(b asc);
...

SQL> insert into foo (a, b) values (...);
...

SQL> select distinct a from foo where b = 'XYZ';
...

111 rows selected.

Elapsed: 00:00:00.50

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (UNIQUE)
   2 1 TABLE ACCESS (FULL) OF 'FOO' Statistics

          0  recursive calls
          5  db block gets
        270  consistent gets
          0  physical reads
          0  redo size
       4815  bytes sent via SQL*Net to client
       1201  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        111  rows processed

SQL> select /*+INDEX(foo)*/ distinct a from foo where b = 'XYZ';

111 rows selected.

Elapsed: 00:00:00.35

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=258)    1 0 SORT (UNIQUE) (Cost=3 Card=1 Bytes=258)

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'FOO' (Cost=1 Card=1 By
          tes=258)

   3    2       BITMAP CONVERSION (TO ROWIDS)
   4    3         BITMAP INDEX (SINGLE VALUE) OF 'FOO_B_IX'

Statistics

          0  recursive calls
          0  db block gets
        270  consistent gets
          0  physical reads
          0  redo size
       4815  bytes sent via SQL*Net to client
       1201  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        111  rows processed

Thanks for your time,

--A. Received on Sat May 27 2000 - 00:00:00 CDT