Re: Analyze problem?

The statistics are based upon what it sees at the time they are run. Any of the following could be your cause.

1. The stats get out of date due to the deletes.
2. The data layout is much better suited for index access, but basic stats don't show it. For example: table with a is_true column (boolean value) true or false. 90% of the rows are True, but you always query on False. Index access would be faster, but Oracle will choose Full Table Scan. 2 values yields 50% rows, hence a full table scan. Rule will naturally use index so it runs better.

Consider adding histograms.

sql> analyze table t_name compute statistics for all indexed columns;

See if that helps.

"Little Prince" <milan1810_at_geocities.com> wrote in message news:9i3lam$24818_at_ctmsun0.macau.ctm.net...
> I use ANALYZE...COMPUTER STATISTICS command to analyze two tables which
 are
> used in a DELETE statement in a batch job. The performance of the job
> obviously drops a lot. The execution time is server times longer than
> before. But, after I use ANALYZE...DELETE STATISTICS to remove the
> statistics, the execution time of the job is much shorter. Anyone can give
> me some advices? As I know statistics can improve performance, why is my
> case?
>
> Andrew
>
>
Received on Sat Jul 07 2001 - 19:01:03 CDT