Re: index selection!

I have run 'analyze table incard compute statistics'

       and 'analyze index idx_bit_incard_if_chk compute statistics' but it does not work!

In article <8p9l9g$c15$1_at_nnrp1.deja.com>,   EnderW <ender29_at_my-deja.com> wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Sep 08 2000 - 21:44:47 CDT