Show TOC

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.

Update the statistics also 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 (see 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 on the Miscellaneous tab page (see Changing Table Properties).

    End of the note.

    The system updates the table statistics.

    Optimizer Statistics

    Property

    Description

    Columns and Indexes

    Columns and indexes

    Different Values

    Estimated number of rows in the table

    If there is a big difference between the value in this column and the value in the Different Values (Exact) column, we recommend that you update the SQL optimizer statistics. For more information, see SQL Optimizer, SQL Optimizer Statistics.

    Different Values (Exact)

    Number of rows in the table (information from the internal file directory)

    Pages

    Estimated table size in pages

    This size affects which search strategy is selected by the system, see SQL Optimizer, Search Strategies.

    If there is a big difference between the value in this column and the value in the Pages (Exact) column, we recommend that you update the SQL optimizer statistics.

    Pages (Exact)

    Table size in pages (information from the internal file directory)

    Last Statistics Update

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

See Activating or Deactivating the Automatic SQL Optimizer Statistics Update.

More Information