SQL Optimizer in the CCMS 

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

Use

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.

The generation of statistics (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.

Prerequisites

DBA Planning Calendar

Setting Up and Using the DBA Planning Calendar

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 to update statistics for individual tables.

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.

See also:

Scheduling concept

Concepts of the Database System, SQL Optimizer Statistics