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 -> Availability of bitmap indexes/parallel server on Linux

Availability of bitmap indexes/parallel server on Linux

From: Alexander Staubo <earlybird_at_mop.no>
Date: 2000/05/29
Message-ID: <oLBY4.3$Xsp.917897@news.randori.com>#1/1

(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

Original text of this message

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