How often to ANALYZE

The information created and stored by the ANALYZE command is static. That is, once generated and stored, the same values will be used until you generate new information. You need to reanalyze after there is a significant change to the data. Of course, what constitutes a significant change to the data is a purely subjective decision. Some purists will claim that you need to reanalyze every night; however, this is probably not cost-effective. Our recommendations are as follows:

  1. Analyze the tables, indexes, and histograms after the first load of the data.

  2. If the tables are updated only on a known periodic basis, say, monthly, in a data warehouse environment, analyze them after the periodic update.

  3. If the tables are updated on an ongoing basis, and the number of rows in the table is changing by more than 5% per week, analyze daily.

  4. If the tables are updated on an ongoing basis, and the number of rows in the table is changing by less than 5% per week, analyze weekly.

This schedule ensures that tables are automatically analyzed.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
52.15.179.139