Re: query did a full table scan even with an index

>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

> In article <8snltv$k3b$1_at_nnrp1.deja.com>,
> Nhuan_at_Lieu NoSpam.org <Nhuan_at_Lieu.org> wrote:
> > Hello, I have an interesting performance tuning issue in Oracle8i an
 d
> > am wonder if someone can provide some insights.
> >
> > Issue: A Select query did a full table scan (expecting it using the
> > index scan) even with an index on the simple WHERE clause.
> >
> > System: NT4 & Oracle8i Enterprise Edition Release 8.1.6.0.0
> >
> > Select Statement:
> > SELECT GL COMPANY NBR,... + ABOUT 30 COLUMNS
> > FROM 1 MILLION REC TABLE
> > WHERE GL COMPANY NBR = '415A';
> >
> > Query count result: 28 rows selected.
> >
> > What have been done before the select statement:
> > 1. The table has 8 indexes, one of them was created using:
> > CREATE INDEX index name ON 1 MILLION REC TABLE(GL COMPANY NBR)
> > NOLOGGING COMPUTE STATISTICS;
> >
> > 2. ANALYZE TABLE 1 MILLION REC TABLE COMPUTE STATISTICS;
> >
> > DATA distribution on the 1 MILLION REC TABLE table:
> > GL COMPANY NBR COUNT(GL COMPANY NBR)
> > ---------------- ---------------------
> > TEST 366
> > 407G 1
> > 415A 28
> > 415G 981796
> >

        The index won't be used because of the extremely low cardinality. The

cost based optimizer has determined that it is more expensive to use the  

index than to not use the index.

        Consider creating a bit-mapped index on this column. Received on Sat Oct 21 2000 - 23:49:01 CDT