Re: Bitmap indexes not being used unless hints forced

Thanks so much for the lucid response. Unfortunately, while it seems you were right about the absence of statistics -- I thought I'd analyzed the tables, but apparently I had not -- I'm getting some weird results.

Finally, the simple "select distinct b from foo where b = 'XYZ';" suddenly prompted the optimizer to use the bitmap index.

It turns out that I had computed statistics for the table columns and indexes, but not the table itself:

    analyze table foo compute statistics for all columns;     analyze table foo compute statistics for all indexes;

When I did this:

    analyze table foo compute statistics;

first and then did the per-column, per-index collection, that cleared things up. Thanks for the nudging me in the right direction. (Aside: Does Oracle provide any means to maintain index statistics? Or do most people use cron or similar scheduling systems for this?)

As for the weird results, the optimizer still isn't all too bright about some queries. For example, where I would expect a bitmap OR to come into play, such as here:

    select distinct b from foo where b in ('XYZ', 'ZYX');

then Oracle will *not* use the bitmap indexes but a full scan, *unless* both "XYZ" and "ZYX" are values that are *not* in the table and index. That's the most puzzling part -- if I specify values that are, in fact, present in the table and index, then the optimizer inexplicably reverts to the full table scan again (and giggles demonically behind my back).

I'm also timing this stuff, and there have been no cases so far where a full table scan has even come close to the bitmap indexes in performance. There's no reason why the optimizer should choose a full table scan at any point, unless it's braindead or the evil spawn of Satan. :-)

And actually, optimizer_mode *is* set to CHOOSE -- it seems to be the default value for Oracle 8i R2.

--A.

"spencer" <spencerp_at_swbell.net> wrote in message news:TT_X4.1181$u35.204880_at_nnrp2.sbc.net...
> the explain for the first query does not include
> any cost estimates, so i'm thinking that Oracle
> has chosen to use the RULE based optimizer.
>
> when optimizer mode is CHOOSE and there
> are no statistics collected on any of the tables
> in the query, Oracle will choose RULE based
> over COST based.
>
> The hint in your second query prompts Oracle to
> use COST based optimization. Since there are
> no statistics available, Oracle is probably scanning
> some portion of the the tables and indexes to come
> up with estimates, and doing this for each parse
> of each new query.
>
> You have three options, in this order of preference:
>
> 1) collect statistics on the table(s) (after loading rows)
> 2) use hints
> 3) change the optimizer mode from choose to cost
>
> actually, i don't recommend option 3... since that will
> force Oracle to use the COST based optimizer for all
> queries... and RULE based seems to work better for
> the system tables.
>
> HTH
>
> "Alexander Staubo" <earlybird_at_mop.no> wrote in message
> news:3%YX4.95$sbi.16056455_at_news.randori.com...
> > 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 Sun May 28 2000 - 00:00:00 CDT