469,610 Members | 1,649 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,610 developers. It's quick & easy.

Why isn't bitmap index being used in this example

I have a table named 'touchpoint' which contains about 20 millions of
rows.

I created a bitmap index on a column named 'reason_cd' which has 7
distinct values: 'IN', 'FR', 'OP', 'CM', 'SV', 'ME', and null.

However, If you take a look at the explain plan for a script like
this, The oracle choose to do full access which is unexpected.

select * from touchpoint where reason_cd in ('OP', 'SV')

ID OPERATION OPTIONS OBJECT_NAME
--- -------------------- -------------------- ---------------
0 SELECT STATEMENT
1 TABLE ACCESS FULL TOUCHPOINT

Funny thing is if I run the following script, it choose to use the
index.

select count(*) from touchpoint where reason-cd in ('OP', 'SV')

ID OPERATION OPTIONS OBJECT_NAME
--- -------------------- -------------------- ----------------
0 SELECT STATEMENT
1 SORT AGGREGATE
2 INLIST ITERATOR
3 BITMAP CONVERSION COUNT
4 BITMAP INDEX SINGLE VALUE IDX_TP_REASON_CD

Can anyone explain what is going on?
Jul 19 '05 #1
2 4708
> I have a table named 'touchpoint' which contains about 20 millions of
rows.

I created a bitmap index on a column named 'reason_cd' which has 7
distinct values: 'IN', 'FR', 'OP', 'CM', 'SV', 'ME', and null.

However, If you take a look at the explain plan for a script like
this, The oracle choose to do full access which is unexpected.

select * from touchpoint where reason_cd in ('OP', 'SV')

ID OPERATION OPTIONS OBJECT_NAME
--- -------------------- -------------------- ---------------
0 SELECT STATEMENT
1 TABLE ACCESS FULL TOUCHPOINT

Funny thing is if I run the following script, it choose to use the
index.

select count(*) from touchpoint where reason-cd in ('OP', 'SV')

ID OPERATION OPTIONS OBJECT_NAME
--- -------------------- -------------------- ----------------
0 SELECT STATEMENT
1 SORT AGGREGATE
2 INLIST ITERATOR
3 BITMAP CONVERSION COUNT
4 BITMAP INDEX SINGLE VALUE IDX_TP_REASON_CD

Can anyone explain what is going on?


Your select count(*) requires only a visit to the bitmap index,
whereas your select * would require both a visit to the bitmap index
and a read of the table data itself (using the rowid pointers). Oracle
visibly thinks that, for the select *, it might as well read the table
record by record (or I should say block by block). If you want to see
by how much the full table scan "wins", turn on event 10053 (and don't
forget that for the log file to be populated, the statement has to be
PARSED, and not only executed - flush the shared pool if necessary).

Daniel
Jul 19 '05 #2
ch********@hotmail.com (chulhee) wrote in message news:<b5**************************@posting.google. com>...
I have a table named 'touchpoint' which contains about 20 millions of
rows.

I created a bitmap index on a column named 'reason_cd' which has 7
distinct values: 'IN', 'FR', 'OP', 'CM', 'SV', 'ME', and null.

However, If you take a look at the explain plan for a script like
this, The oracle choose to do full access which is unexpected.

select * from touchpoint where reason_cd in ('OP', 'SV')

ID OPERATION OPTIONS OBJECT_NAME
--- -------------------- -------------------- ---------------
0 SELECT STATEMENT
1 TABLE ACCESS FULL TOUCHPOINT

Funny thing is if I run the following script, it choose to use the
index.

select count(*) from touchpoint where reason-cd in ('OP', 'SV')

ID OPERATION OPTIONS OBJECT_NAME
--- -------------------- -------------------- ----------------
0 SELECT STATEMENT
1 SORT AGGREGATE
2 INLIST ITERATOR
3 BITMAP CONVERSION COUNT
4 BITMAP INDEX SINGLE VALUE IDX_TP_REASON_CD

Can anyone explain what is going on?


The two plans you show make me think that the statistics show the
table is fairly small so accessing the data is faster performing a
full table scan rather than via the bitmap index. While just scanning
the index is faster for counting hits since no data is requested which
in turn means table access is not required at all.

You might want to compare your actual row count to the
dba_tables.num_rows column. If the table is small you need more data
for testing.

HTH -- Mark D Powell --
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Prashant | last post: by
14 posts views Thread by ford_desperado | last post: by
10 posts views Thread by Troels Arvin | last post: by
6 posts views Thread by Lespaul36 | last post: by
6 posts views Thread by \Frank\ | last post: by
4 posts views Thread by krishhhna | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.