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

Excellent insight, Mr. Lewis, with regard to why I had restricted my histogram to just 8 columns. Short answer: I didn't do enough indepth research on my part on that area.

Anyhow, your suggestion in letting Oracle do the sizing did the trick -- -- the query performance has dropped from about 112 seconds to .3 seconds! Thank you all for the great works.

In addition, I had also reverted back to the orginal B-tree index as bitmap indexing was not 100% appropriate for the situation. Althought the performance on this query is barely increase with the bitmap indexing technique, but it was done for because the table is not 100% read-only as records are being insert to it on a weekly basis.

In article <972207155.20966.0.nnrp-03.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> 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. Received on Mon Oct 23 2000 - 11:21:46 CDT