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

Sorry,
  I havenot checked your query as it appears you use a covered index, therefore my suggestion is worthless. There is a cost based optimizer_cost_adj_indx variable or something. You can set this value to a higher value, it is actually at 100, this will make it lean to index. However this would lead a change in all queries.   If hint is ok, give explicit index hint (specify your index).

In article <8sq831$nhk$1_at_nnrp1.deja.com>,   JustMe <Nhuan_at_Lieu.org> wrote:
> 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.
>

--
Ender Wiggin


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