Re: OPTIMIZER / ANALYZE INDEX

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
>
Received on Wed Jun 10 1998 - 06:52:56 CDT