index selection!

From: <wy_at_fudan.edu>
Date: Fri, 08 Sep 2000 02:17:55 GMT
Message-ID: <8p9i82$8f0$1@nnrp1.deja.com>

I have a table with a field which value must be 'T' ,'F' the table has about 70,000 records.(about less than 100 records have the value 'F');
I have create a bitmap index on it.
the problem is :
when I execute 'select count(*) from tabname where fieldname='F',it uses the bitmap index.
the execution plan:
1.28 SELECT STATEMENT Optimizer =CHOOSE Cost=28   2.1 SORT AGGREGATE
    3.1 BITMAP CONVERSION COUNT
      4.1 BITMAP INDEX SINGLE VALUE IDX_BIT_INCARD_IF_CHK It runs fast.

when I execute 'select * from tabname where fieldname='F' '

           or  'select sum(fields1) from tabname where fieldname='F'
           ,it uses full access table

the execute plan :
1.630 SELECT STATEMENT Optimizer =CHOOSE Cost=630   2.1 TABLE ACCESS FULL INCARD
It runs not so fast.

when I execute 'select /*+ index(incard idx_bit_incard_if_chk) */

• from incard where if_chk='F'', It select the bitmap index. the execute plan: 1.4488 SELECT STATEMENT Optimizer =CHOOSE Cost=4488 2.1 TABLE ACCESS BY INDEX ROWID INCARD 3.1 BITMAP CONVERSION TO ROWIDS 4.1 BITMAP INDEX SINGLE VALUE IDX_BIT_INCARD_IF_CHK It runs fast.

I think that oracle should automatic select the best execute plan (uses the correct index).
otherwise I should change every sql statement(using the table) in the program.
How should I do?

Any suggestions are appreciated.

wy.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Sep 07 2000 - 21:17:55 CDT