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

Hi

I rather suspect that using a Bitmap index is a mistake. You should really only be using these for static tables.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
"JustMe" <Nhuan_at_Lieu.org> wrote in message
news:8sq831$nhk$1_at_nnrp1.deja.com...


> Well, I am getting close, but no cigars. I have implemented the

> followings per many your good suggestions (in that order):

>

> 1. Analyze table 1_MILLION_ROW_TABLE delete statistics;

> 2. Drop the index;

> 3. Analyze table 1_MILLION_ROW_TABLE compute statistics for columns

> gl_local_acct_nbr size 8;

> 4. Recreated the index. This time using the keyword BITMAP;

> 5. Rerun query, but table scan still exists.

> 6. Tried using ALL_ROWS hint (confirm that it did use it in EXPLAIN

> PLAN result) with the above steps in tact, table scan exists as

> expected since HINT ignore index, performance increase only 1-2%.

> 7. Repeat the process with "analyze table 1_MILLION_ROW_TABLE compute

> statistics for all indexed columns"

>

> Any other suggestions would be appreciated and thanks for those who

> already helped.

>

>

> In article <8so1h9$u4g$1_at_nnrp1.deja.com>,

>   EnderW <ender29_at_my-deja.com> wrote:

> > analyze table tableX delete statistics

> > analyze table tableX compute statistics for all indexed columns

> >

> > should do the trick

> >

> > In article <8sns7e$pno$1_at_news.gte.com>,

> >   "Kevin Brand" <kevin.brandx_at_tel.gte.com> wrote:

> > >

> > > Histogram on GL_COMPANY_NBR should do the trick.  Be aware that

 CHOOSE tends


> > > to favour ALL_ROWS rather than FIRST_ROWS ( at least in OR7 ), which

 can


> > > result in full scans.

> > >

> > > You may want to first try putting a FIRST_ROWS hint in the SQL.

> > >

> > > -Kevin

> > >

> > > "Nhuan_at_Lieu_NoSpam.org" <Nhuan_at_Lieu.org> wrote in message

> > > news:8snltv$k3b$1_at_nnrp1.deja.com...

> > > > Hello, I have an interesting performance tuning issue in Oracle8i

 and


> > > > 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

> > > >

> > > > Data from SET TIMING ON & EXPLAIN PLAN:

> > > > Elapsed: 00:01:119.20

> > > > Execution Plan

> > > > ---------------------------------------------------------

> > > >    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8514 Card=327397

> > > > Bytes=127357433)

> > > >    1    0   TABLE ACCESS* (FULL) OF '1_MILLION_REC_TABLE'

> > > > (Cost=8514 :Q178000 Card=327397 Bytes=127357433)

> > > >

> > > >

> > > > Statistics

> > > > -----------------------------------------------------

> > > >         787  recursive calls

> > > >          69  db block gets

> > > >       56224  consistent gets

> > > >       56099  physical reads

> > > >         684  redo size

> > > >       13504  bytes sent via SQL*Net to client

> > > >         696  bytes received via SQL*Net from client

> > > >           3  SQL*Net roundtrips to/from client

> > > >          21  sorts (memory)

> > > >           0  sorts (disk)

> > > >          28  rows processed

> > > >

> > > >

> > > > Sent via Deja.com http://www.deja.com/

> > > > Before you buy.

> > >

> > >

> >

> > --

> > Ender Wiggin

> >

> > Sent via Deja.com http://www.deja.com/

> > Before you buy.

> >

>

> --

> Remove no "_NoSpam" to email.

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.