Re: Need opinions on "Compute Statistics"

On Wed, 04 Jun 2003 08:11:31 +0000, aoshell wrote:

> I would like to get some opinions on the usage of the ANALYZE
> STATISTICS command. I am using Oracle 8.1.6 and am doing some data
> loads for large tables.
>
> Procedure is designed as follows:
> - truncate table, drop indexes, insert data, recreate indexes, ANALYZE
> STATISTICS
>
> 1. If I use a statement like "ANALYZE TABLE A_PRODUCT_DETAILS COMPUTE
> STATISTICS" is this computing the statistics on the indexes also? If
> not, do I really need to since I am recreating them just prior to
> issuing the statement and have done a TRUNCATE which is supposed to
> RESET indexes?
>
> 2. What is the school of though on ESTIMATING statistics and what
> percent would be recommended for the fastest execution while retaining
> some level of accurracy. Largest table is about 2 million rows and is
> taking about 23 minutes.
>
> 3. Should i be using the DBMS_STATS package instead and if so why?
>
> Thanks for all input - I can read the Oracle manuals but it's nice to
> get some opinions from those in the field.
>
> Cheers!

You should be using dbms_stats as analyze has some bugs especially with long raw and so on . Also dbms_stats could be run in parallel and monitoring could be turned on so that when a certain % of the table changes then it could analyze automagically .

Good Luck ! Received on Wed Jun 04 2003 - 11:51:28 CDT