Re: Analyse takes a long time

From: Steven Hauser <hause011_at_garnet.tc.umn.edu>
Date: 1 Aug 2002 12:15:15 -0500
Message-ID: <aibqb3$rik$1@garnet.tc.umn.edu>

Ask yourself: What is the purpose of ANALYZE?

It is to get the distribution statistics for the data in the tables so the optimizer can find a quick path to the data.

If the size of the tables do not change drastically and the distribution of values do not change drastically the best way to optimize runtime of ANALYZE is to run it fewer times. Instead of twice a week, run it once a week. (50% faster!) Or once a month or split the job to one table a week. Or just once. (100% faster since you have already run it!)

If a table is fluctuating in size by magnitudes of "X" and/or attribute value distribution has a "large" difference over time ANALYZE that table more often.

Also if it is run on a sample instead of the whole data set it also will run faster.

This strategy applies to all RDBMS products that use statistics to optimize data retrival. Received on Thu Aug 01 2002 - 12:15:15 CDT