OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » BITMAP INDEX

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

BITMAP INDEX [message #325279]

Thu, 05 June 2008 09:16

bm50793
Messages: 7
Registered: June 2008

Junior Member

Hi all,

can anyone please help me on the below issue

iam having a table in my database (tp_billing_data)

data in one of the columns (file_source) is having low cardinality and the values are 2,3,5,0,4 .

As the column is having low cardinality we decided to go for a bit map index and crated as given below

create bitmap index in_bid_fs
on tp_billing_data(file_source)
tablespace &cdb_idx
storage (initial 128K
next 128K
pctincrease 0)
pctfree 0
/

After creating index i've executed the below..

18:48:14 SQL> analyze table tp_billing_data compute statistics
18:52:08 2 /

Table analyzed.

18:52:12 SQL> select count(*) from tp_billing_data;

COUNT(*)
----------
18981

1* select count(*) from tp_billing_data where file_source=2
18:56:01 SQL> /

COUNT(*)
----------
2903

18:56:06 SQL> set autot traceonly explain
18:57:00 SQL> select * from tp_billing_data where file_source =2
18:57:20 2 /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=3797 Bytes=6
15114)

1 0 TABLE ACCESS (FULL) OF 'TP_BILLING_DATA' (Cost=27 Card=379
7 Bytes=615114)



and found that oracle is not using the index

later i tried as below

18:58:26 SQL> analyze table tp_billing_data compute statistics for all indexes
18:59:09 2 /

Table analyzed.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=3797 Bytes=6
15114)

1 0 TABLE ACCESS (FULL) OF 'TP_BILLING_DATA' (Cost=27 Card=379
7 Bytes=615114)



19:00:30 SQL> analyze table tp_billing_data compute statisticts for all indexed columns
19:01:12 2 /

19:02:06 SQL> select * from tp_billing_data where file_source =2
19:02:26 2 /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=2903 Bytes=4
70286)

1 0 TABLE ACCESS (FULL) OF 'TP_BILLING_DATA' (Cost=27 Card=290
3 Bytes=470286)

still i found nothing new with my query except decrease in the cardinality value

Any one please suggest ...........

thanks in advance

[Updated on: Thu, 05 June 2008 09:43] by Moderator

Report message to a moderator

Re: BITMAP INDEX [message #325282 is a reply to message #325279]

Thu, 05 June 2008 09:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

use CUT & PASTE & post results from

select file_source , count(*) from tp_billing_data group by file_source ;

Report message to a moderator

Re: BITMAP INDEX [message #325285 is a reply to message #325279]

Thu, 05 June 2008 09:36

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

And i would use dbms_stats instead of 'Analyze'.

Report message to a moderator

Re: BITMAP INDEX [message #325348 is a reply to message #325282]

Thu, 05 June 2008 23:02

bm50793
Messages: 7
Registered: June 2008

Junior Member

Hi Anacedent,

thanks for your interest...

09:26:56 SQL> select file_source , count(*) from tp_billing_data group by file_source ;

FILE_SOURCE COUNT(*)
----------- ----------
0 8670
2 2903
3 185
4 2649
5 4574

Report message to a moderator

Re: BITMAP INDEX [message #325349 is a reply to message #325285]

Thu, 05 June 2008 23:05

bm50793
Messages: 7
Registered: June 2008

Junior Member

Hi Mahesh,

i tried even that also...please find the below

09:23:45 SQL> exec dbms_stats.gather_index_stats(OWNNAME=>'GLOBAL_CDB', INDNAME=>'IN_BID_FS')

PL/SQL procedure successfully completed.

09:23:51 SQL> set autot traceonly exp
09:24:08 SQL> select * from tp_billing_data where file_source=2;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TP_BILLING_DATA'

still no difference......

Report message to a moderator

Re: BITMAP INDEX [message #325353 is a reply to message #325348]

Thu, 05 June 2008 23:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

bm50793 wrote on Thu, 05 June 2008 21:02

I suspect that the only chance to use the index is FILE_SOURCE=3

Realize that sometimes a Full Table Scan is most efficient.

Report message to a moderator

Re: BITMAP INDEX [message #325354 is a reply to message #325353]

Thu, 05 June 2008 23:26

bm50793
Messages: 7
Registered: June 2008

Junior Member

Hi Anacedent

using file source 3 also made no difference

09:46:19 SQL> select * from tp_billing_data where file_source=3;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=3797 Bytes=6
15114)

1 0 TABLE ACCESS (FULL) OF 'TP_BILLING_DATA' (Cost=27 Card=379
7 Bytes=615114)

As you said ....this is may be due to FULL TABLE SCAN efficiency over index scan...

Anyway many thanks for all your suggestions.....

Report message to a moderator

Re: BITMAP INDEX [message #325990 is a reply to message #325354]

Mon, 09 June 2008 15:51

JackyShu
Messages: 25
Registered: May 2008

Junior Member

it is data related, which means your data distribution, schema, statistics...
first, we have a narrow table (), we see oracle think use table scan is more efficient.
note: i udpated some rows to make data not evenly distributed.

sys@abc> drop table t;

Table dropped.

sys@abc> 
sys@abc> create table t
  2  as
  3  select floor(dbms_random.value(0,5)) f1, rpad(' ', 4, '*') f2
  4  from dba_objects;

Table created.

sys@abc> 
sys@abc> select f1, count(*) from t group by f1;

        F1   COUNT(*)
---------- ----------
         1      10050
         2      10233
         4      10122
         3      10106
         0      10067

sys@abc> 
sys@abc> 
sys@abc> update t set f1 = 1 where f1 <> 1 and rownum < 15000;

14999 rows updated.

sys@abc> 
sys@abc> select f1, count(*) from t group by f1;

        F1   COUNT(*)
---------- ----------
         1      25049
         2       6444
         4       6373
         3       6395
         0       6317

sys@abc> 
sys@abc> 
sys@abc> create bitmap index bix_t
  2  on t(f1);

Index created.

sys@abc> 
sys@abc> exec dbms_stats.gather_index_stats(OWNNAME=>'SYS', INDNAME=>'BIX_T

PL/SQL procedure successfully completed.

sys@abc> 
sys@abc> set autotrace traceonly
sys@abc> 
sys@abc> select * from t where f1 = 3;

6395 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 69930149

------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Ti
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |  5926 |    98K|    21   (0)| 00
|   1 |  TABLE ACCESS BY INDEX ROWID | T     |  5926 |    98K|    21   (0)| 00
|   2 |   BITMAP CONVERSION TO ROWIDS|       |       |       |            |     
|*  3 |    BITMAP INDEX SINGLE VALUE | BIX_T |       |       |            |     
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("F1"=3)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
        158  recursive calls
          0  db block gets
        571  consistent gets
          0  physical reads
          0  redo size
     121018  bytes sent via SQL*Net to client
       5063  bytes received via SQL*Net from client
        428  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       6395  rows processed

sys@abc> 
sys@abc> select * from t where f1 = 1;

25049 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 24471 |   406K|    23   (9)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 24471 |   406K|    23   (9)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("F1"=1)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
       1829  consistent gets
          0  physical reads
          0  redo size
     269176  bytes sent via SQL*Net to client
      18736  bytes received via SQL*Net from client
       1671  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      25049  rows processed

sys@abc> 
sys@abc> set autotrace off

second, we make a wide table, now table full scan is not efficient. we see oracle use index even it has too extract lots rows.

sys@abc> drop table t;

Table dropped.

sys@abc> 
sys@abc> create table t
  2  as
  3  select floor(dbms_random.value(0,5)) f1, rpad(' ', 4, '*') f2
  4  from dba_objects;

Table created.

sys@abc> 
sys@abc> select f1, count(*) from t group by f1;

        F1   COUNT(*)
---------- ----------
         1      10050
         2      10233
         4      10122
         3      10106
         0      10067

sys@abc> 
sys@abc> 
sys@abc> update t set f1 = 1 where f1 <> 1 and rownum < 15000;

14999 rows updated.

sys@abc> 
sys@abc> select f1, count(*) from t group by f1;

        F1   COUNT(*)
---------- ----------
         1      25049
         2       6444
         4       6373
         3       6395
         0       6317

sys@abc> 
sys@abc> 
sys@abc> create bitmap index bix_t
  2  on t(f1);

Index created.

sys@abc> 
sys@abc> exec dbms_stats.gather_index_stats(OWNNAME=>'SYS', INDNAME=>'BIX_T')

PL/SQL procedure successfully completed.

sys@abc> 
sys@abc> set autotrace traceonly
sys@abc> 
sys@abc> select * from t where f1 = 3;

6395 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 69930149

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |  5926 |    98K|    21   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T     |  5926 |    98K|    21   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|       |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | BIX_T |       |       |            |          |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("F1"=3)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
        158  recursive calls
          0  db block gets
        571  consistent gets
          0  physical reads
          0  redo size
     121018  bytes sent via SQL*Net to client
       5063  bytes received via SQL*Net from client
        428  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       6395  rows processed

sys@abc> 
sys@abc> select * from t where f1 = 1;

25049 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 24471 |   406K|    23   (9)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 24471 |   406K|    23   (9)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("F1"=1)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
       1829  consistent gets
          0  physical reads
          0  redo size
     269176  bytes sent via SQL*Net to client
      18736  bytes received via SQL*Net from client
       1671  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      25049  rows processed

sys@abc> 
sys@abc> set autotrace off
sys@abc> 
sys@abc> 
sys@abc> 
sys@abc> 
sys@abc> drop table t;

Table dropped.

sys@abc> 
sys@abc> create table t
  2  as
  3  select floor(dbms_random.value(0,5)) f1, rpad(' ', 4000, '*') f2
  4  from dba_objects;

Table created.

sys@abc> 
sys@abc> select f1, count(*) from t group by f1;

        F1   COUNT(*)
---------- ----------
         1      10133
         2      10050
         4      10139
         3      10180
         0      10076

sys@abc> 
sys@abc> 
sys@abc> update t set f1 = 1 where f1 <> 1 and rownum < 15000;

14999 rows updated.

sys@abc> 
sys@abc> select f1, count(*) from t group by f1;

        F1   COUNT(*)
---------- ----------
         1      25132
         2       6334
         4       6376
         3       6386
         0       6350

sys@abc> 
sys@abc> 
sys@abc> create bitmap index bix_t
  2  on t(f1);

Index created.

sys@abc> 
sys@abc> exec dbms_stats.gather_index_stats(OWNNAME=>'SYS', INDNAME=>'BIX_T')

PL/SQL procedure successfully completed.

sys@abc> 
sys@abc> set autotrace traceonly
sys@abc> 
sys@abc> select * from t where f1 = 3;

6386 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 69930149

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |  4817 |  9478K|  1232   (1)| 00:00:15 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T     |  4817 |  9478K|  1232   (1)| 00:00:15 |
|   2 |   BITMAP CONVERSION TO ROWIDS|       |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | BIX_T |       |       |            |          |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("F1"=3)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
        160  recursive calls
          0  db block gets
       6492  consistent gets
       6718  physical reads
          0  redo size
   25760603  bytes sent via SQL*Net to client
       5052  bytes received via SQL*Net from client
        427  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       6386  rows processed

sys@abc> 
sys@abc> select /* rule */ * from t where f1 = 1;

25132 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 69930149

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       | 23282 |    44M|  1240   (1)| 00:00:15 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T     | 23282 |    44M|  1240   (1)| 00:00:15 |
|   2 |   BITMAP CONVERSION TO ROWIDS|       |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | BIX_T |       |       |            |          |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("F1"=1)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
      25228  consistent gets
      25141  physical reads
          0  redo size
  101379491  bytes sent via SQL*Net to client
      18802  bytes received via SQL*Net from client
       1677  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      25132  rows processed

sys@abc> 
sys@abc> set autotrace off
sys@abc> 
sys@abc>

Report message to a moderator

Previous Topic:	Related To STATSPACK
Next Topic:	About tkprof

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 27 20:56:26 CDT 2024