Procedure documentationUpdating SQL Optimizer Statistics Locate this document in the navigation structure

 

The database system needs the SQL Optimizer statistics to determine the optimal search strategy for accessing data when executing complex SQL statements.

Recommendation Recommendation

Update the statistics once a week.

Also update the statistics in the following cases:

  • If the size proportions or values in the database tables have changed significantly

  • If you have upgraded the database (for example, from version 7.6 to version 7.7)

Update the statistics at a time of minimal work load.

End of the recommendation.

Prerequisites

  • You are logged on to the database as database system administrator or as a DBM operator with server authorization for accessing an SQL session.

    More information: Glossary, Server Permission

  • The database is in the ONLINE operational state.

Procedure

Updating Statistics for a Specific Table

  1. In the context menu of the table, choose Definition.

  2. Open the Optimizer Statistics tab page.

  3. Choose Update in the context menu of the Table Statistics field.

  4. Define the sample type and sample size that you want the system to use to generate the statistics.

    Sample Type and Size for Updating the SQL Optimizer Statistics

    Number of Data Records (Sample Type Rows)

    Proportion of Data Records as Percentage of all Data Records in the Table (Sample Size Percent)

    Description

    0

    0

    The system does not update any statistics for the table.

    0 < Number of data records < Number of data records in the table

    The default value for the sample size is 20,000 data records.

    0 < Percentage of data records < 100 %

    For large tables (> 1,000,000 data records) in SAP systems, we recommend a sample size of 5 %.

    The system uses the sample size specified to estimate the statistics.

    Number of all data records in the table

    100 %

    The system calculates the statistics. This usually takes much longer than an estimation using a sample.

    Note Note

    You defined default values when you created the table. To change these default values, change the table properties (in the Miscellaneous tab page).

    More information: Changing Table Properties

    End of the note.

    The system updates the table statistics.

    Optimizer Statistics

    Property

    Description

    Columns and Indexes

    Columns and indexes

    Distinct Values

    Estimated number of lines in the table

    Page Count

    Estimated table size (in pages)

    This size affects which search strategy is selected by the system.

    More information: SQL Optimizer, Search Strategies

    Last Statistics Update

    Time of the last statistics update
Activating or Deactivating Automatic Statistics Update for All Tables

  1. In the context menu of the database, choose Administration.

  2. Open the Overview tab page.

  3. To activate the automatic update, choose Activate automatic statistics update.

    To deactivate the automatic update, choose Deactivate automatic statistics update.

More Information