Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Bitmap indexes not being used unless hints forced

Re: Bitmap indexes not being used unless hints forced

From: spencer <spencerp_at_swbell.net>
Date: 2000/05/27
Message-ID: <TT_X4.1181$u35.204880@nnrp2.sbc.net>#1/1

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 Sat May 27 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US