Using the DB2 Query Optimizer (Understanding Query Plans)

The query optimizer is a key component in db2, responsible for analyzing SQL statements and generating execution plans that best fit the database table structure.

Understanding Types of Queries

There are two main types of queries: single statement and multi-statement. A single statement refers to one containing only a SELECT, INSERT, UPDATE or DELETE statement, such as


SELECT * FROM users WHERE id = 1;

Multi-Statement Statements

Multi-statement statements refer to those that contain multiple statements, for example:


SELECT * FROM users WHERE id = 1;
INSERT INTO orders (user_id, order_date) VALUES (1, '2022-01-01');
UPDATE orders SET status = 'pending' WHERE user_id = 1;

Understanding Execution Plans

An execution plan refers to the final sequence of operations and actions generated by the optimizer. It contains key information analyzed by the optimizer, such as indexes, data locations, and access patterns.

Components in an Execution Plan

The execution plan typically includes the following components:

1. Select

  • SELECT ：
  • select_type: indicates type (ALL, SIMPLE etc.)
  • tabletype: table type (system table, indexed table etc.)
  • possible_keys: list of potentially used indexes

2. Join

  • join_type: join type (INNER JOIN, LEFT JOIN etc.)
  • tables: list of tables participating in the join
  • on: join condition

3. ORDER BY

  • type: order by type (ALL, FIRST etc.)
  • columns: list of sort columns
  • use_index: whether to use index or not

4. LIMIT

  • limit_type: limit type (ALL, FIRST etc.)
  • rows: number of returned records

How to Understand and Analyze Execution Plans

Understanding and analyzing execution plans is crucial for optimizing Queries. Here are some suggestions:

1. Understanding Select

  • Through select_type and tabletype, you can determine which tables need to be accessed.
  • Through possible_keys, you can determine which indexes might be used.

2. Analyzing Join

  • Through join_type and tables, you can determine the join type and participating tables.
  • Through on, you can determine the join condition.

3. Analyzing ORDER BY

  • Through type and columns, you can determine sort columns and sorting method.
  • Through use_index, you can determine whether to use index or not.

4. Analyzing LIMIT

  • Through limit_type and rows, you can determine the type and quantity of returned records.

Comprehensive Analysis of Execution Plans

Comprehensive analysis of execution plans helps us:

  • Determine which tables need to be accessed.
  • Determine which indexes might be used.
  • Determine join types and participating tables.
  • Determine sort columns and sorting method.
  • Determine the type and quantity of returned records.

By analyzing the execution plan, we can make adjustments to improve Queries performance.