Re: Need opinions on "Compute Statistics"

On 4 Jun 2003 08:11:31 -0700, aoshell2_at_yahoo.com wrote:

Answers embedded

>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?
NO. You need the cascade option

 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?

Not ANALYZing the indexes would make the ANALYZE TABLE pretty useless.

The same applies to statistics without histograms.

>
>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.

run the ANALYZE during off-peak hours. I must laugh about making a problem of 23 minutes analyze. Please keep in mind MANY statististical attributes are INACCURATE using estimate.

>
>3. Should i be using the DBMS_STATS package instead and if so why?

ANALYZE has been desupported by Oracle. DBMS_STATS generates more accurate statistics, can be run in parallel and you can play around with multiple variants.
>
>Thanks for all input - I can read the Oracle manuals but it's nice to
>get some opinions from those in the field.
>

I think 80 percent of my answers above come directly from the manual. Please keep in mind Usenet is a volunteer business.

>Cheers!

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Jun 04 2003 - 12:42:43 CDT