Home » RDBMS Server » Performance Tuning » Composite bitmap indexes
Composite bitmap indexes [message #122136] Fri, 03 June 2005 03:23 Go to next message
tansdot
Messages: 2
Registered: June 2005
Junior Member
Can you create a bitmap index on two columns both with a few distinct values.
eg:

COlumn x : values 1,2,3,4
Column y: values N,S,E,W


Does it make sense to create a composite bitmap index on the above two columns?

Thanks
Re: Composite bitmap indexes [message #122151 is a reply to message #122136] Fri, 03 June 2005 07:46 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
Yes, you can. It makes sense if you often
use conditions like "(x=<<>> and y=<<>>) or (x=<<>> and y=<<>>)"
or like "x=<<>> or x=<<>>" (considering x is the first column in the index).

SQL> select count(1) from bin_idx_tab;

  COUNT(1)
----------
      9001

SQL> select unique * from bin_idx_tab;

         A B
---------- --
         1 A
         1 B
         1 C
         2 A
         2 B
         2 C
         3 A
         3 B
         3 C

9 rows selected.

SQL> create bitmap index btm01 on bin_idx_tab(a,b);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'bin_idx_tab', cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly
SQL> select * from bin_idx_tab where (a=1 and b='C') or (a=2 and b='B');

2000 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=2000 Bytes=
          10000)

   1    0   BITMAP CONVERSION (TO ROWIDS) (Cost=1 Card=2000 Bytes=1000
          0)

   2    1     BITMAP INDEX (FAST FULL SCAN) OF 'BTM01' (INDEX (BITMAP)
          )

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
      26852  bytes sent via SQL*Net to client
       1971  bytes received via SQL*Net from client
        135  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2000  rows processed

SQL> drop index btm01;

Index dropped.

SQL> select * from bin_idx_tab where (a=1 and b='C') or (a=2 and b='B');

2000 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=2000 Bytes=
          10000)

   1    0   TABLE ACCESS (FULL) OF 'BIN_IDX_TAB' (TABLE) (Cost=6 Card=
          2000 Bytes=10000)

Statistics
----------------------------------------------------------
        158  recursive calls
          0  db block gets
        174  consistent gets
          0  physical reads
          0  redo size
      26852  bytes sent via SQL*Net to client
       1971  bytes received via SQL*Net from client
        135  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       2000  rows processed


Rgds.
Re: Composite bitmap indexes [message #122430 is a reply to message #122136] Mon, 06 June 2005 08:49 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I'd like to add to the above (which I think Dmitry already implied) that in creating a composite bitmap, you lose some flexibility with the total number of possible queries and combinations that may be useful to you, depending of course on your situation. This is particularly true when you get to 3 or more columns. So you may want to test having separate bitmaps for each column vs one or two composite indexes.
Previous Topic: can use 2 GB as SGA(4gb ram) oracle 8i windows 2000s
Next Topic: How to read the execution plan
Goto Forum:
  


Current Time: Thu Mar 28 14:55:57 CDT 2024