Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Bitmap indexes not being used unless hints forced
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:
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