Re: Why isn't bitmap index being used in this example

chulheekim_at_hotmail.com (chulhee) wrote in message news:<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?

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 -- Received on Tue May 04 2004 - 01:37:44 CEST