Result set Estimation for JOIN statements in the DB2 optimizer (1)

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 (it can be analogous to a BMW 730 or Boeing 747 engine ). 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 the intermediate result set and the third table for join 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.

A join predicate is generally described as follows:

 
 

  
  
T1.joincol=T2.joincol 
 
 

In practice, in addition to JOIN predicates, the Where clause generally has local predicates, as follows:

 
 

  
  
T1.joincol=T2.joincol and T1.filter=literal_1 and T2.filter=literal_2 
 
 

The predicate T1.filter = literal_1 is used to filter T1 tables. T2.filter = literal_2 is used to filter multiple T2 tables, and then two filtered tables are joined. Whether to use hash JOIN or Merge join or NestLoop Join for Join depends on the DB2 optimization level, parameter settings, and cost estimation.

The selective calculation formula for DB2 Join predicates is as follows:

 
 

  
  
Selectivity (T1.y = T2.y)= 1/max(colcard(T1. joincol), colcard(T2. joincol)) 
 
 

Colcard (T1. joincol) indicates the number of different values in the joincol column of Table T1, and colcard (T2. joincol) indicates the number of different values in the joincol column of Table T2, the two take a larger one as the basis for calculating Join predicates. This formula has two assumptions:

Include, that is, all values of T2. joincol are within the value range of T1 joincol, and vice versa.

Balanced, that is, the data distribution on the two join columns is even.

Result set Estimation for JOIN statements in the DB2 Optimizer

Author: Yan hongqing, source: IT expert network forum, responsible editor: Chen Ziqi

When the DB2 optimizer generates an execution plan for an SQL statement, it evaluates the result set size of each step, which is the base estimate of the DB2 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.

The formula for calculating the DB2 Join predicate base estimation is as follows:

 
 

  
  
Join Cardinality =Join Selectivity *   
  
  
filtered cardinality(t1) *   
  
  
filtered cardinality(t2)  
 
 

Here, filtered cardinality (t1) is used to obtain the result set after applying the local predicate on table T1, and filtered cardinality (t2) is used to obtain the result set after applying the local predicate on table T2.

Example