Why isn't bitmap index being used in this example

[chulhee]

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_C D

Can anyone explain what is going on?

[Daniel Roy]

> 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_C D

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

[Mark D Powell]

ch********@hotm ail.com (chulhee) wrote in message news:<b5******* *************** ****@posting.go ogle.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_C D

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 --