Why isn't bitmap index being used in this example

From: chulhee <chulheekim_at_hotmail.com>
Date: 3 May 2004 11:41:07 -0700
Message-ID: <b57d06da.0405031041.2d331e0d@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? Received on Mon May 03 2004 - 13:41:07 CDT