Procedure documentationSQL Optimizer in CCMS Locate this document in the navigation structure

 

SQL statements describe which information data must contain. They do not, however, describe how to read or write that data. Therefore, there has to be an instance for converting SQL statements into actual access operations. That instance is the SQL Optimizer.

Since each SQL statement has to be converted into different access operations, the runtime can vary depending on the amount of data to be processed. The SQL optimizer has the task of processing the requested data by using the most cost-efficient strategy.

The database system uses the cost-based SQL optimizer exclusively. This means that there are no rules for how to access the data. Accesses can be made to simple tables (base tables) or to multiple tables (joins or subqueries). The aim of the SQL optimizer is to find the most cost-effective plan for processing access to the tables, including those involved in the join.

The optimizer uses statistics to determine a search strategy for access to data from multiple tables.

UPDATE STATISTICS

The cost-based SQL optimizer uses statistics that are generated by the UPDATE STATISTICS statements.

Statistics generation (UPDATE STATISTICS) can be triggered by the administrator (UPDATE STATISTICS <table> or UPDATE STATISTICS <column>).

You can start an UPDATE STATISTICS run from the SAP system.

Procedure

You can schedule UPDATE STATISTICS runs in the DBA Planning Calendar: Scheduling Updates of the SQL Optimizer Statistics

In exceptional situations you can use the Tables/Views/Synonyms user menu (DBA Cockpit/Database/liveCache Assistant) to update statistics for individual tables.

Note Note

When an SAP system is installed or upgraded, the statistics that are required by the SQL optimizer have to be generated for all tables. These actions are documented in the installation and upgrade documentation, and are not covered here.

End of the note.

More Information

Planning Concept

Concepts of the Database System, SQL Optimizer, SQL Optimizer Statistics

SQL Reference Manual, UPDATE STATISTICS Statement

SAP Note 808060