It looks as if you need to generate a histogram
on the T/F column. At present Oracle is probably
assuming that you have 35,000 rows at each value.
--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
wy_at_fudan.edu wrote in message <8p9i82$8f0$1_at_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 Fri Sep 08 2000 - 02:38:23 CDT
