Why isn't bitmap index being used in this example
Date: 3 May 2004 11:41:07 -0700
Message-ID: <b57d06da.0405031041.2d331e0d_at_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 - 20:41:07 CEST