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

Why did you restrict your histogram to just 8 columns ? Virtually all your data is for value 415G, by using only 8 columns, you restrict Oracle to thinking that 12.5% (one eighth) of your data may cover the other three values as well- consequently Oracle has probably assumed that ca. 3% of your data is for the requested value.

Given that you seem to have declared this table as a parallel table, and given the very high number of rows that Oracle thinks it will have to acquire about 30,000 rows it is not too surprising that it still does a scan.

If you can control the code based on the input value, add an 'index' hint for the company numbers where there are only a few rows.

Otherwise go back to the b-tree index, and generate a histogram with a lot more columns (in fact, if you don't specify the size, you may find that Oracle will decide on about 500 columns, although it will probably store about 3 of them, since most of the end-points will have the same value).

BTW - look at the stats in you explain plan -

>> > > TABLE ACCESS* (FULL) OF '1_MILLION_REC_TABLE'
>> > > (Cost=8514 :Q178000 Card=327397 Bytes=127357433)

The Card entry tells you that under your original setup Oracle though it was going to find 327,397 rows - one third of the total; that's why it did a tablescan. (Presumably the original estimate missed the one entry for 407G, so Oracle had a cardinality of 3 for the column).

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

JustMe wrote in message <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.