Re: index selection!

Well that depends on the optimizer plan. try analyzing the whole table rather than estimating. It should help.

In article <8p9i97$8f7$1_at_nnrp1.deja.com>,   wy_at_fudan.edu wrote:
> 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.
>

--
Ender Wiggin


Sent via Deja.com http://www.deja.com/
Before you buy.