Classic version of JOIN result set Estimation in DB2 optimizer!

The following articles mainly describe the result set estimation of JOIN statements in the DB2 optimizer. In actual operations, when the DB2 optimizer generates an execution plan for SQL statements, in actual operation, the result set generated by each step is estimated in size, which is the basis estimation of the optimizer.

In the basic estimation process of all SQL statements, the calculation process of JOIN statements is the most complex, and JOIN statements are the focus of performance optimization. This article focuses on the calculation methods and inputs used by the DB2 optimizer for base estimation.

Introduction

The optimizer is the heart and soul of DB2 and can be analogous to the engine of the BMW 730 or Boeing 747 ). It analyzes SQL statements and determines the most effective access path for each statement. The DB2 SQL optimizer can estimate the execution cost of each alternative access plan and select an optimal access plan based on the estimated results.

The optimizer uses two very important concepts in optimizing an SQL statement: selectivity and cardinality. Selectivity refers to the percentage of the result set obtained by an SQL operation to the original result set, while cardinality refers to the number of rows of the result set obtained by an SQL operation.

To correctly determine the cost of each access plan, the DB2 optimizer estimates the number of returned rows in the result set generated by each step, which is the basis estimation of the optimizer. The DB2 optimizer requires an accurate base estimate. Base estimation is a process in which the optimizer uses statistics to determine the size of partial query results after a predicate is applied or aggregation is executed. For each operator in the access plan, the optimizer estimates the base output of the operator. Applications with one or more predicates can reduce the output stream base.

JOIN predicates

When we need to join multiple tables in SQL, DB2 will first select two of them for join and obtain an intermediate result set, then DB2 may use this intermediate result set and the third table for join operations, and obtain the intermediate result set again. Of course, the other two tables may also be joined, and then perform the join operation on the two intermediate result sets. No matter how the operation is performed, the number of tables that DB2 can join at a time must be two. Therefore, when the optimizer considers how to handle the Join operation, the join sequence is a very important issue, because we always hope to control the result set as little as possible at the beginning.

The above content is an introduction to the result set estimation of the JOIN statement in the DB2 optimizer. I hope you will get something.