Re: 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?

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 Received on Tue May 04 2004 - 00:32:19 CEST