Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Availability of bitmap indexes/parallel server on Linux
The "bitmap indexes" feature is included as part of Oracle 8i Enterprise Edition, not as part of the Oracle Parallel Server option.
From whom did you hear that bitmap indexes are not available on Linux ?
"Alexander Staubo" <earlybird_at_mop.no> wrote in message
news:oLBY4.3$Xsp.917897_at_news.randori.com...
> (Although I'm currently evaluating Oracle on Windows, we're
developing
> on Linux, and I have yet to install Oracle on that OS.)
>
> Now, I hear that bitmap indexes only come with the parallel
server
> (OPS) option, but apparently OPS does not exist on Linux quite
yet.
> Does this mean that bitmap indexes are not supported on Linux
yet?
>
> --A.
>
> "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 Mon May 29 2000 - 00:00:00 CDT