Using EXPLAIN to understand the access path
You can use the EXPLAIN statement to determine the access paths for the SELECT parts of your statements.
This information describes what EXPLAIN provides and how you can obtain information from EXPLAIN. The information in the plan table can help you when you need to perform the following tasks:
- Determine the access path that Db2 chooses for a query
- Design databases, indexes, and application programs
- Determine when to rebind an application
For each access to a single table, EXPLAIN indicates whether Db2 uses index access or a table space scan. For indexes, EXPLAIN indicates how many indexes and index columns are used and what I/O methods are used to read the pages. For joins of tables, EXPLAIN indicates the join method and type, the order in which Db2 joins the tables, and the occasions when and reasons why it sorts any rows.
The following steps summarize how to obtain information from EXPLAIN:
- Create the plan table.
Before you can use EXPLAIN, you must create a plan table to hold the results of EXPLAIN.
- Populate the plan table.
You can populate the plan table by executing the SQL statement EXPLAIN. You can also populate a plan table when you bind or rebind a plan or package by specifying the option EXPLAIN(YES). EXPLAIN obtains information about the access paths for all explainable SQL statements in a package or in the DBRMs of a plan.
- Select information from the plan table.
Several processes can insert rows into the same plan table. To understand access paths, you must retrieve the rows for a particular query in an appropriate order.
EXPLAIN helps you answer questions about query performance; the answers give you the information that you need to make performance improvements. EXPLAIN indicates whether Db2 used an index to access data, whether sorts were performed, whether parallel processing was used, and so on.
As you gain experience working with Db2, you can use the plan table to give optimization hints to Db2 that influence access path selection.