Re: OPTIMIZER / ANALYZE INDEX

In article <357E7398.697418C9_at_cressoft.com.pk>,   Umar FArooq <umar.farooq_at_cressoft.com.pk> wrote:
>
> It is also possible that the after using the ANALYZE statistics the
> optimizer thinks that not using the index would be optimal. For example,
> if the query returns even 40-50% of the rows in the table then probably
> a full table scan in quicker. A lot would depend on the cloumns being
> returned and the exact search condition. For example, if your query
> returns only one extra column apart from the date column then try
> concatentating the extra column within the data index. This would
> prevent teh extraneous disk reads for the table blocks to acquire the
> extra column data. Pl. post the exact query and the index statements.
>
> Umar Farooq Ch.
> Software Engineer
> CresSoft, Lahore, Pakistan
> umar.farooq_at_cressoft.com.pk
>
> Venkat wrote:
>
> > ESTIMATE STATISTICS without a sample size clause collects statistics
> > for just a small
> > fraction of the records. Try using ANALYZE INDEX index_name ESTIMATE
> > STATISTICS SAMPLE 20
> > PERCENT (or more). Better yet COMPUTE STATISTICS if you can spare the
> > time. My guess is
> > the optimizer just does not have enough information to make efficient
> > use of the index.
> >
> > Venkat
> >
>
>

Thanks for your interest.

The table in question has 3 million rows representing 10 years worth of data. The select statement is select count(*) from the_table where the_date > some_date (where some_date is so far in the past that the resulting count will be a count of all rows). There is an index on the_date. When no statistics are collected on the index, the optimizer uses the index. When statistics are collected, either estimated or computed, the optimizer does a full table scan.

Thanks again

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Thu Jun 11 1998 - 17:42:21 CDT