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

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.