Four examples are shown here to help understand the layout and format of the output from db2expln and dynexpln. These examples were run against the SAMPLE database as provided with DB2. A brief discussion is provided for each example. Significant differences from one example to the next have been shown in bold.
This example is simply requesting a list of all employee names, their jobs, department name and location, and the project name(s) on which they are working. The essence of this access plan is that hash joins are used to join the relevant data from each of the specified tables. Since no indexes are available, the access plan does a relation scan of each table.
******************** PACKAGE *************************************** Package Name = QUERY.DYNEXPLN Prep Date = 1998/06/09 Prep Time = 11:14:16:037 Bind Timestamp = 1998-06-09-11.14.16.371570 Isolation Level = Cursor Stability Blocking = Block Unambiguous Cursors Query Optimization Class = 5 Partition Parallel = No Intra-Partition Parallel = No Function Path = "SYSIBM", "SYSFUN", "QUERY" -------------------- SECTION --------------------------------------- Section = 1 SQL Statement: SELECT X.LASTNAME, X.JOB, Y.DEPTNAME, Y.LOCATION, Z.PROJNAME FROM EMPLOYEE X, DEPARTMENT Y, PROJECT Z WHERE X.WORKDEPT = Y.DEPTNO AND X.WORKDEPT = Z.DEPTNO AND Y.DEPTNO = Z.DEPTNO Estimated Cost = 119 Estimated Cardinality = 109 Access Table Name = QUERY.EMPLOYEE ID = 5 | #Columns = 3 | Relation Scan | | Prefetch: Eligible | Lock Intents | | Table: Intent Share | | Row : Next Key Share | Process Build Table for Hash Join Hash Join | Estimated Build Size: 7167 | Estimated Probe Size: 9011 | Access Table Name = QUERY.PROJECT ID = 7 | | #Columns = 2 | | Relation Scan | | | Prefetch: Eligible | | Lock Intents | | | Table: Intent Share | | | Row : Next Key Share | | Process Build Table for Hash Join | Hash Join | | Estimated Build Size: 5789 | | Estimated Probe Size: 6414 | | Access Table Name = QUERY.DEPARTMENT ID = 4 | | | #Columns = 3 | | | Relation Scan | | | | Prefetch: Eligible | | | Lock Intents | | | | Table: Intent Share | | | | Row : Next Key Share | | | Process Probe Table For Hash Join Return Data to Application | #Columns = 5 End of section Optimizer Plan: RETURN ( 1) | HSJOIN ( 2) / \ HSJOIN TBSCAN ( 3) ( 6) / \ | TBSCAN TBSCAN Table: ( 4) ( 5) QUERY | | EMPLOYEE Table: Table: QUERY QUERY DEPARTMENT PROJECT
The first part of the plan accesses the EMPLOYEE table to build a hash join Build table. Then the PROJECT table is accessed, and hash join builds another Build table. Finally, the DEPARTMENT table is scanned, and lookups are performed on the Build tables, first on the one created from PROJECT, then on the one created from EMPLOYEE. When the application completes, the qualifying rows are returned.
This example shows the same SQL statement as "Example One: "No Parallelism" Plan", but this query has been compiled for a 4-way SMP machine.
******************** PACKAGE *************************************** Package Name = QUERY.DYNEXPLN Prep Date = 1998/06/09 Prep Time = 11:21:20:032 Bind Timestamp = 1998-06-09-11.21.20.322036 Isolation Level = Cursor Stability Blocking = Block Unambiguous Cursors Query Optimization Class = 5 Partition Parallel = No Intra-Partition Parallel = Yes (Bind Degree = 4) Function Path = "SYSIBM", "SYSFUN", "QUERY" -------------------- SECTION --------------------------------------- Section = 1 SQL Statement: SELECT X.LASTNAME, X.JOB, Y.DEPTNAME, Y.LOCATION, Z.PROJNAME FROM EMPLOYEE X, DEPARTMENT Y, PROJECT Z WHERE X.WORKDEPT = Y.DEPTNO AND X.WORKDEPT = Z.DEPTNO AND Y.DEPTNO = Z.DEPTNO Intra-Partition Parallelism Degree = 4 Estimated Cost = 129 Estimated Cardinality = 109 Process Using 4 Subagents | Access Table Name = QUERY.EMPLOYEE ID = 5 | | #Columns = 3 | | Parallel Scan | | Relation Scan | | | Prefetch: Eligible | | Lock Intents | | | Table: Intent Share | | | Row : Next Key Share | | Process Build Table for Hash Join | Hash Join | | Estimated Build Size: 7167 | | Estimated Probe Size: 9011 | | Access Table Name = QUERY.PROJECT ID = 7 | | | #Columns = 2 | | | Parallel Scan | | | Relation Scan | | | | Prefetch: Eligible | | | Lock Intents | | | | Table: Intent Share | | | | Row : Next Key Share | | | Process Build Table for Hash Join | | Hash Join | | | Estimated Build Size: 5789 | | | Estimated Probe Size: 6414 | | | Access Table Name = QUERY.DEPARTMENT ID = 4 | | | | #Columns = 3 | | | | Parallel Scan | | | | Relation Scan | | | | | Prefetch: Eligible | | | | Lock Intents | | | | | Table: Intent Share | | | | | Row : Next Key Share | | | | Process Probe Table For Hash Join | Insert Into Asynchronous Local Table Queue ID = q1 Access Local Table Queue ID = q1 #Columns = 5 Return Data to Application | #Columns = 5 End of section Optimizer Plan: RETURN ( 1) | TQ ( 2) | HSJOIN ( 3) / \ HSJOIN TBSCAN ( 4) ( 7) / \ | TBSCAN TBSCAN Table: ( 5) ( 6) QUERY | | EMPLOYEE Table: Table: QUERY QUERY DEPARTMENT PROJECT
This plan is almost identical to the plan in the first example. The main differences are the creation of four subagents when the plan first starts and the table queue at the end of the plan to gather the results of each of subagent's work before returning them to the application.
This example shows the same SQL statement as "Example One: "No Parallelism" Plan", but this query has been compiled on a partitioned database made up of four database partitions.
******************** PACKAGE *************************************** Package Name = QUERY.DYNEXPLN Prep Date = 1998/06/09 Prep Time = 11:27:30:058 Bind Timestamp = 1998-06-09-11.27.30.583713 Isolation Level = Cursor Stability Blocking = Block Unambiguous Cursors Query Optimization Class = 5 Partition Parallel = Yes Intra-Partition Parallel = No Function Path = "SYSIBM", "SYSFUN", "QUERY" -------------------- SECTION --------------------------------------- Section = 1 SQL Statement: SELECT X.LASTNAME, X.JOB, Y.DEPTNAME, Y.LOCATION, Z.PROJNAME FROM EMPLOYEE X, DEPARTMENT Y, PROJECT Z WHERE X.WORKDEPT = Y.DEPTNO AND X.WORKDEPT = Z.DEPTNO AND Y.DEPTNO = Z.DEPTNO Buffered Insert = No Estimated Cost = 111 Estimated Cardinality = 190 Coordinator Subsection: Distribute Subsection #2 | Broadcast to Node List | | Nodes = 10, 20, 30, 40 Distribute Subsection #3 | Broadcast to Node List | | Nodes = 10, 20, 30, 40 Distribute Subsection #1 | Broadcast to Node List | | Nodes = 10, 20, 30, 40 Access Table Queue ID = q1 #Columns = 5 Return Data to Application | #Columns = 5 Subsection #1: Access Table Queue ID = q2 #Columns = 2 Hash Join | Estimated Build Size: 5789 | Estimated Probe Size: 7632 | Access Table Queue ID = q3 #Columns = 3 | Hash Join | | Estimated Build Size: 5333 | | Estimated Probe Size: 6414 | | Access Table Name = QUERY.DEPARTMENT ID = 4 | | | #Columns = 3 | | | Relation Scan | | | | Prefetch: Eligible | | | Lock Intents | | | | Table: Intent Share | | | | Row : Next Key Share | | | Process Probe Table For Hash Join Insert Into Asynchronous Table Queue ID = q1 | Broadcast to Coordinator Node | Rows Can Overflow to Temporary Table Subsection #2: Access Table Name = QUERY.PROJECT ID = 7 | #Columns = 2 | Relation Scan | | Prefetch: Eligible | Lock Intents | | Table: Intent Share | | Row : Next Key Share | Insert Into Asynchronous Table Queue ID = q2 | | Hash to Specific Node | | Rows Can Overflow to Temporary Tables Insert Into Asynchronous Table Queue Completion ID = q2 Subsection #3: Access Table Name = QUERY.EMPLOYEE ID = 5 | #Columns = 3 | Relation Scan | | Prefetch: Eligible | Lock Intents | | Table: Intent Share | | Row : Next Key Share | Insert Into Asynchronous Table Queue ID = q3 | | Hash to Specific Node | | Rows Can Overflow to Temporary Tables Insert Into Asynchronous Table Queue Completion ID = q3 End of section Optimizer Plan: RETURN ( 1) | TQ ( 2) | HSJOIN ( 3) / \ HSJOIN TQ ( 4) ( 8) / \ | TBSCAN TQ TBSCAN ( 5) ( 6) ( 9) | | | Table: TBSCAN Table: QUERY ( 7) QUERY DEPARTMENT | PROJECT Table: QUERY EMPLOYEE
This plan has all the same pieces as the plan in the first example, but the section has been broken into four subsections. The subsections have the following tasks:
This example shows the same SQL statement as "Example One: "No Parallelism" Plan", but this query has been compiled on a partitioned database made up of four database partitions.
******************** PACKAGE *************************************** Package Name = QUERY.DYNEXPLN Prep Date = 1998/06/09 Prep Time = 11:30:33:077 Bind Timestamp = 1998-06-09-11.30.33.770876 Isolation Level = Cursor Stability Blocking = Block Unambiguous Cursors Query Optimization Class = 5 Partition Parallel = Yes Intra-Partition Parallel = Yes (Bind Degree = 4) Function Path = "SYSIBM", "SYSFUN", "QUERY" -------------------- SECTION --------------------------------------- Section = 1 SQL Statement: SELECT X.LASTNAME, X.JOB, Y.DEPTNAME, Y.LOCATION, Z.PROJNAME FROM EMPLOYEE X, DEPARTMENT Y, PROJECT Z WHERE X.WORKDEPT = Y.DEPTNO AND X.WORKDEPT = Z.DEPTNO AND Y.DEPTNO = Z.DEPTNO Buffered Insert = No Estimated Cost = 111 Estimated Cardinality = 190 Coordinator Subsection: Distribute Subsection #2 | Broadcast to Node List | | Nodes = 10, 20, 30, 40 Distribute Subsection #3 | Broadcast to Node List | | Nodes = 10, 20, 30, 40 Distribute Subsection #1 | Broadcast to Node List | | Nodes = 10, 20, 30, 40 Access Table Queue ID = q1 #Columns = 5 Return Data to Application | #Columns = 5 Subsection #1: Process Using 3 Subagents | Access Table Queue ID = q3 #Columns = 2 | Hash Join | | Estimated Build Size: 5789 | | Estimated Probe Size: 7632 | | Access Table Queue ID = q5 #Columns = 3 | | Hash Join | | | Estimated Build Size: 5333 | | | Estimated Probe Size: 6414 | | | Access Table Name = QUERY.DEPARTMENT ID = 4 | | | | #Columns = 3 | | | | Parallel Scan | | | | Relation Scan | | | | | Prefetch: Eligible | | | | Lock Intents | | | | | Table: Intent Share | | | | | Row : Next Key Share | | | | Process Probe Table For Hash Join | Insert Into Asynchronous Local Table Queue ID = q2 Access Local Table Queue ID = q2 #Columns = 5 Insert Into Asynchronous Table Queue ID = q1 | Broadcast to Coordinator Node | Rows Can Overflow to Temporary Table Subsection #2: Process Using 3 Subagents | Access Table Name = QUERY.PROJECT ID = 7 | | #Columns = 2 | | Parallel Scan | | Relation Scan | | | Prefetch: Eligible | | Lock Intents | | | Table: Intent Share | | | Row : Next Key Share | Insert Into Asynchronous Local Table Queue ID = q4 Access Local Table Queue ID = q4 #Columns = 2 Insert Into Asynchronous Table Queue ID = q3 | Hash to Specific Node | Rows Can Overflow to Temporary Tables Subsection #3: Process Using 3 Subagents | Access Table Name = QUERY.EMPLOYEE ID = 5 | | #Columns = 3 | | Parallel Scan | | Relation Scan | | | Prefetch: Eligible | | Lock Intents | | | Table: Intent Share | | | Row : Next Key Share | Insert Into Asynchronous Local Table Queue ID = q6 Access Local Table Queue ID = q6 #Columns = 3 Insert Into Asynchronous Table Queue ID = q5 | Hash to Specific Node | Rows Can Overflow to Temporary Tables End of section Optimizer Plan: RETURN ( 1) | TQ ( 2) | TQ ( 3) | HSJOIN ( 4) / \ HSJOIN TQ ( 5) ( 10) / \ | TBSCAN TQ TQ ( 6) ( 7) ( 11) | | | Table: TQ TBSCAN QUERY ( 8) ( 12) DEPARTMENT | | TBSCAN Table: ( 9) QUERY | PROJECT Table: QUERY EMPLOYEE
This plan is similar to that in "Example Three: Multipartition Database Plan with Inter-Partition Parallelism", except that multiple subagents execute each subsection. Also, at the end of each subsection, a local table queue gathers the results from all of the subagents before the qualifying rows are inserted into the second table queue to be hashed to a specific node.