管理の手引き


db2expln および dynexpln 出力の例

db2expln および dynexpln からの出力のレイアウトと形式をご理解いただくために、 5 つの例を示します。 これらの例は、DB2 で提供される SAMPLE データベースに対して実行されたものです。 それぞれの例について、簡単な説明が添えられています。 1 つの例と次の例との重要な相違点は、太字で示してあります。

例 1: 「非並列」プラン

この例は、全従業員の名前、職種、部門名とその場所、 および現在携わっているプロジェクト名のリストを要求するだけのものです。 このアクセス・プランの特徴は、 指定したそれぞれの表から関係するデータを結合するのにマージ結合を使用するという点です。 索引を使用することができないので、アクセス・プランは各表の関係走査を行います。 各表は結合される前に分類される必要があります。

******************** PACKAGE ***************************************
 
Package Name = DOOLE.DYNEXPLN
	Prep Date = 2000/01/03
	Prep Time = 15:47:58
 
	Bind Timestamp = 2000-01-03-15.47.58.607455
 
	Isolation Level          = Cursor Stability
	Blocking                 = Block Unambiguous Cursors
	Query Optimization Class = 5
 
	Partition Parallel       = No
	Intra-Partition Parallel = No
 
	Function Path            = "SYSIBM", "SYSFUN", "DOOLE"
 
-------------------- SECTION ---------------------------------------
Section = 1
 
 
SQL Statement:  
  
  SELECT x.lastname, x.job, y.deptname, y.location, z.projname 
  FROM employee AS x, department AS y, project AS z 
  WHERE x.workdept = y.deptno AND x.workdept = z.deptno AND y.deptno 
          = z.deptnos
 
 
Estimated Cost        = 126
Estimated Cardinality = 153
 
Access Table Name = DOOLE.DEPARTMENT  ID = 2,4
|  #Columns = 3
|  Relation Scan
|  |  Prefetch: Eligible
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Insert Into Sorted Temp Table  ID = t1
|  |  #Columns = 3
|  |  #Sort Key Columns = 1
|  |  |  Key 1: DEPTNO (Ascending)
|  |  Sortheap Allocation Parameters:
|  |  |  #Rows     = 40
|  |  |  Row Width = 48
|  |  Piped
Sorted Temp Table Completion  ID = t1
Access Temp Table  ID = t1
|  #Columns = 3
|  Relation Scan
|  |  Prefetch: Eligible
Merge Join
|  Access Table Name = DOOLE.PROJECT  ID = 2,7
|  |  #Columns = 2
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  |  Insert Into Sorted Temp Table  ID = t2
|  |  |  #Columns = 2
|  |  |  #Sort Key Columns = 1
|  |  |  |  Key 1: DEPTNO (Ascending)
|  |  |  Sortheap Allocation Parameters:
|  |  |  |  #Rows     = 38
|  |  |  |  Row Width = 28
|  |  |  Piped
|  Sorted Temp Table Completion  ID = t2
|  Access Temp Table  ID = t2
|  |  #Columns = 2
|  |  Relation Scan
|  |  |  Prefetch: Eligible
Merge Join
|  Access Table Name = DOOLE.EMPLOYEE  ID = 2,5
|  |  #Columns = 3
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  |  Insert Into Sorted Temp Table  ID = t3
|  |  |  #Columns = 3
|  |  |  #Sort Key Columns = 1
|  |  |  |  Key 1: WORKDEPT (Ascending)
|  |  |  Sortheap Allocation Parameters:
|  |  |  |  #Rows     = 63
|  |  |  |  Row Width = 32
|  |  |  Piped
|  Sorted Temp Table Completion  ID = t3
|  Access Temp Table  ID = t3
|  |  #Columns = 3
|  |  Relation Scan
|  |  |  Prefetch: Eligible
Return Data to Application
|  #Columns = 5
 
End of section
 
 
Optimizer Plan:
 
               RETURN 
               (   1) 
                 |    
               MSJOIN 
               (   2) 
              /      \
        MSJOIN         TBSCAN 
        (   3)         (  12) 
       /      \          |    
   TBSCAN    TBSCAN     SORT  
   (   4)    (   8)    (  13) 
     |         |         |    
    SORT      SORT     TBSCAN 
   (   5)    (   9)    (  14) 
     |         |         |    
   TBSCAN    TBSCAN   Table:   
   (   6)    (  10)   DOOLE   
     |         |      EMPLOYEE 
 Table:      Table:  
 DOOLE       DOOLE  
 DEPARTMENT  PROJECT 

アクセス・プランの最初の部分では、DEPARTMENT および PROJECT 表にアクセスし、 マージ結合を使ってそれらの表を結合します。 この結合の結果は EMPLOYEE 表に結合されます。 結果行はアプリケーションに戻されます。

例 2: 区画内並行処理による単一区画データベースのプラン

この例は、例 1: 「非並列」プランと同じ SQL ステートメントを示していますが、 この照会は、4-way の SMP マシン用にコンパイルされたものです。

******************** PACKAGE ***************************************
 
Package Name = DOOLE.DYNEXPLN
	Prep Date = 2000/01/03
	Prep Time = 15:48:51
 
	Bind Timestamp = 2000-01-03-15.48.51.402403
 
	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", "DOOLE"
 
-------------------- SECTION ---------------------------------------
Section = 1
 
 
SQL Statement:  
  
  SELECT x.lastname, x.job, y.deptname, y.location, z.projname 
  FROM employee AS x, department AS y, project AS z 
  WHERE x.workdept = y.deptno AND x.workdept = z.deptno AND y.deptno 
          = z.deptno
 
Intra-Partition Parallelism Degree = 4
 
Estimated Cost        = 142
Estimated Cardinality = 153
 
Process Using 4 Subagents
|  Access Table Name = DOOLE.DEPARTMENT  ID = 2,4
|  |  #Columns = 3
|  |  Parallel Scan
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  |  Insert Into Sorted Shared Temp Table  ID = t1
|  |  |  #Columns = 3
|  |  |  #Sort Key Columns = 1
|  |  |  |  Key 1: DEPTNO (Ascending)
|  |  |  Use Round-Robin Sort
|  |  |  Sortheap Allocation Parameters:
|  |  |  |  #Rows     = 40
|  |  |  |  Row Width = 48
|  |  |  Piped
|  Sorted Shared Temp Table Completion  ID = t1
|  Access Temp Table  ID = t1
|  |  #Columns = 3
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  Merge Join
|  |  Access Table Name = DOOLE.PROJECT  ID = 2,7
|  |  |  #Columns = 2
|  |  |  Parallel Scan
|  |  |  Relation Scan
|  |  |  |  Prefetch: Eligible
|  |  |  Lock Intents
|  |  |  |  Table: Intent Share
|  |  |  |  Row  : Next Key Share
|  |  |  Insert Into Sorted Shared Temp Table  ID = t2
|  |  |  |  #Columns = 2
|  |  |  |  #Sort Key Columns = 1
|  |  |  |  |  Key 1: DEPTNO (Ascending)
|  |  |  |  Use Replicated Sort
|  |  |  |  Sortheap Allocation Parameters:
|  |  |  |  |  #Rows     = 38
|  |  |  |  |  Row Width = 28
|  |  |  |  Piped
|  |  Sorted Shared Temp Table Completion  ID = t2
|  |  Access Temp Table  ID = t2
|  |  |  #Columns = 2
|  |  |  Relation Scan
|  |  |  |  Prefetch: Eligible
|  Insert Into Sorted Shared Temp Table  ID = t3
|  |  #Columns = 5
|  |  #Sort Key Columns = 1
|  |  |  Key 1: (Ascending)
|  |  Use Partitioned Sort
|  |  Sortheap Allocation Parameters:
|  |  |  #Rows     = 61
|  |  |  Row Width = 72
|  |  Piped
|  Access Temp Table  ID = t3
|  |  #Columns = 5
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  Merge Join
|  |  Access Table Name = DOOLE.EMPLOYEE  ID = 2,5
|  |  |  #Columns = 3
|  |  |  Parallel Scan
|  |  |  Relation Scan
|  |  |  |  Prefetch: Eligible
|  |  |  Lock Intents
|  |  |  |  Table: Intent Share
|  |  |  |  Row  : Next Key Share
|  |  |  Insert Into Sorted Shared Temp Table  ID = t4
|  |  |  |  #Columns = 3
|  |  |  |  #Sort Key Columns = 1
|  |  |  |  |  Key 1: WORKDEPT (Ascending)
|  |  |  |  Use Partitioned Sort
|  |  |  |  Sortheap Allocation Parameters:
|  |  |  |  |  #Rows     = 63
|  |  |  |  |  Row Width = 32
|  |  |  |  Piped
|  |  Sorted Shared Temp Table Completion  ID = t4
|  |  Access Temp Table  ID = t4
|  |  |  #Columns = 3
|  |  |  Relation Scan
|  |  |  |  Prefetch: Eligible
|  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) 
                 |    
                LTQ   
               (   2) 
                 |    
               MSJOIN 
               (   3) 
              /      \
        TBSCAN        TBSCAN 
        (   4)        (  15) 
          |             |    
         SORT          SORT  
        (   5)        (  16) 
          |             |    
        MSJOIN        TBSCAN 
        (   6)        (  17) 
       /      \         |    
   TBSCAN    TBSCAN  Table:   
   (   7)    (  11)  DOOLE   
     |         |     EMPLOYEE 
    SORT      SORT  
   (   8)    (  12) 
     |         |    
   TBSCAN    TBSCAN 
   (   9)    (  13) 
     |         |    
 Table:      Table:  
 DOOLE       DOOLE  
 DEPARTMENT  PROJECT 

このプランは、最初の例のプランとほとんど同じです。 主な相違は、プランが最初に開始されるときに 4 つのサブエージェントを作成すること、 および、アプリケーションに戻す前におのおののサブエージェントの作業の結果を収集するために、 プランの終了時に表待ち行列を作成することです。

興味深い別の点として、EMPLOYEE と結合する前には余分の分類が必要になります。 この分類が必要になるのは、 DEPARTMENT と PROJECT のマージ結合を処理するサブエージェントにより、 異なる順序で行が結合される可能性があるからです。

例 3: 区画間並行処理による複数区画データベースのプラン

この例は、例 1: 「非並列」プランと同じ SQL ステートメントを示していますが、 この照会は、3 つのデータベース区画からなる区分データベースでコンパイルされたものです。

******************** PACKAGE ***************************************
 
Package Name = DOOLE.DYNEXPLN
	Prep Date = 2000/01/03
	Prep Time = 15:21:29
 
	Bind Timestamp = 2000-01-03-15.21.29.990983
 
	Isolation Level          = Cursor Stability
	Blocking                 = Block Unambiguous Cursors
	Query Optimization Class = 5
 
	Partition Parallel       = Yes
	Intra-Partition Parallel = No
 
	Function Path            = "SYSIBM", "SYSFUN", "DOOLE"
 
-------------------- SECTION ---------------------------------------
Section = 1
 
 
SQL Statement:  
  
  SELECT x.lastname, x.job, y.deptname, y.location, z.projname 
  FROM employee AS x, department AS y, project AS z 
  WHERE x.workdept = y.deptno AND x.workdept = z.deptno AND y.deptno 
          = z.deptno
 
 
Estimated Cost        = 118
Estimated Cardinality = 263
 
Coordinator Subsection:
   Distribute Subsection #2
   |  Broadcast to Node List
   |  |  Nodes = 13, 82, 193
   Distribute Subsection #3
   |  Broadcast to Node List
   |  |  Nodes = 13, 82, 193
   Distribute Subsection #1
   |  Broadcast to Node List
   |  |  Nodes = 13, 82, 193
   Access Table Queue  ID = q1  #Columns = 5
   Return Data to Application
   |  #Columns = 5
 
Subsection #1:
   Access Table Queue  ID = q2  #Columns = 3
   |  Output Sorted
   |  |  #Key Columns = 1
   |  |  |  Key 1: (Ascending)
   Merge Join
   |  Access Table Name = DOOLE.DEPARTMENT  ID = 2,4
   |  |  #Columns = 3
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
   |  |  Lock Intents
   |  |  |  Table: Intent Share
   |  |  |  Row  : Next Key Share
   |  |  Insert Into Sorted Temp Table  ID = t1
   |  |  |  #Columns = 3
   |  |  |  #Sort Key Columns = 1
   |  |  |  |  Key 1: DEPTNO (Ascending)
   |  |  |  Sortheap Allocation Parameters:
   |  |  |  |  #Rows     = 40
   |  |  |  |  Row Width = 48
   |  |  |  Piped
   |  Sorted Temp Table Completion  ID = t1
   |  Access Temp Table  ID = t1
   |  |  #Columns = 3
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
   Merge Join
   |  Access Table Queue  ID = q3  #Columns = 2
   |  |  Output Sorted
   |  |  |  #Key Columns = 1
   |  |  |  |  Key 1: (Ascending)
   Insert Into Asynchronous Table Queue  ID = q1
   |  Broadcast to Coordinator Node
   |  Rows Can Overflow to Temporary Table
 
Subsection #2: 
   Access Table Name = DOOLE.EMPLOYEE  ID = 2,5
   |  #Columns = 3
   |  Relation Scan
   |  |  Prefetch: Eligible
   |  Lock Intents
   |  |  Table: Intent Share
   |  |  Row  : Next Key Share
   |  Insert Into Sorted Temp Table  ID = t2
   |  |  #Columns = 3
   |  |  #Sort Key Columns = 1
   |  |  |  Key 1: WORKDEPT (Ascending)
   |  |  Sortheap Allocation Parameters:
   |  |  |  #Rows     = 27
   |  |  |  Row Width = 32
   |  |  Piped
   Sorted Temp Table Completion  ID = t2
   Access Temp Table  ID = t2
   |  #Columns = 3
   |  Relation Scan
   |  |  Prefetch: Eligible
   |  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 = DOOLE.PROJECT  ID = 2,7
   |  #Columns = 2
   |  Relation Scan
   |  |  Prefetch: Eligible
   |  Lock Intents
   |  |  Table: Intent Share
   |  |  Row  : Next Key Share
   |  Insert Into Sorted Temp Table  ID = t3
   |  |  #Columns = 2
   |  |  #Sort Key Columns = 1
   |  |  |  Key 1: DEPTNO (Ascending)
   |  |  Sortheap Allocation Parameters:
   |  |  |  #Rows     = 38
   |  |  |  Row Width = 28
   |  |  Piped
   Sorted Temp Table Completion  ID = t3
   Access Temp Table  ID = t3
   |  #Columns = 2
   |  Relation Scan
   |  |  Prefetch: Eligible
   |  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) 
                 |    
                BTQ   
               (   2) 
                 |    
               MSJOIN 
               (   3) 
              /      \
       MSJOIN           MDTQ  
       (   4)          (  14) 
      /      \           |    
   MDTQ      TBSCAN    TBSCAN 
  (   5)     (  10)    (  15) 
    |          |         |    
  TBSCAN      SORT      SORT  
  (   6)     (  11)    (  16) 
    |          |         |    
   SORT      TBSCAN    TBSCAN 
  (   7)     (  12)    (  17) 
    |          |         |    
  TBSCAN   Table:      Table:  
  (   8)   DOOLE       DOOLE  
    |      DEPARTMENT  PROJECT 
 Table:   
 DOOLE   
 EMPLOYEE 

このプランは、最初の例のプランと全く同じ内容ですが、 セクションが 4 つのサブセクションに分けられています。 サブセクションは、次のようなタスクを行います。

例 4: 区画間並行処理と区画内並行処理による複数区画データベースのプラン

この例は、例 1: 「非並列」プランと同じ SQL ステートメントを示していますが、この照会は、 3 つのデータベース区画 (4-way SMP マシン上にある) から成る区分データベースでコンパイルされたものです。

******************** PACKAGE ***************************************
 
Package Name = DOOLE.DYNEXPLN
	Prep Date = 2000/01/03
	Prep Time = 15:22:14
 
	Bind Timestamp = 2000-01-03-15.22.14.659970
 
	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", "DOOLE"
 
-------------------- SECTION ---------------------------------------
Section = 1
 
 
SQL Statement:  
  
  SELECT x.lastname, x.job, y.deptname, y.location, z.projname 
  FROM employee AS x, department AS y, project AS z 
  WHERE x.workdept = y.deptno AND x.workdept = z.deptno AND y.deptno 
          = z.deptno
 
Intra-Partition Parallelism Degree = 4
 
Estimated Cost        = 140
Estimated Cardinality = 263
 
Coordinator Subsection:
   Distribute Subsection #2
   |  Broadcast to Node List
   |  |  Nodes = 13, 82, 193
   Distribute Subsection #3
   |  Broadcast to Node List
   |  |  Nodes = 13, 82, 193
   Distribute Subsection #1
   |  Broadcast to Node List
   |  |  Nodes = 13, 82, 193
   Access Table Queue  ID = q1  #Columns = 5
   Return Data to Application
   |  #Columns = 5
 
Subsection #1:
   Process Using 4 Subagents
   |  Access Table Queue  ID = q3  #Columns = 3
   |  Insert Into Sorted Shared Temp Table  ID = t1
   |  |  #Columns = 3
   |  |  #Sort Key Columns = 1
   |  |  |  Key 1: (Ascending)
   |  |  Use Partitioned Sort
   |  |  Sortheap Allocation Parameters:
   |  |  |  #Rows     = 27
   |  |  |  Row Width = 32
   |  |  Piped
   |  Access Temp Table  ID = t1
   |  |  #Columns = 3
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
   |  Merge Join
   |  |  Access Table Name = DOOLE.DEPARTMENT  ID = 2,4
   |  |  |  #Columns = 3
   |  |  |  Parallel Scan
   |  |  |  Relation Scan
   |  |  |  |  Prefetch: Eligible
   |  |  |  Lock Intents
   |  |  |  |  Table: Intent Share
   |  |  |  |  Row  : Next Key Share
   |  |  |  Insert Into Sorted Shared Temp Table  ID = t2
   |  |  |  |  #Columns = 3
   |  |  |  |  #Sort Key Columns = 1
   |  |  |  |  |  Key 1: DEPTNO (Ascending)
   |  |  |  |  Use Partitioned Sort
   |  |  |  |  Sortheap Allocation Parameters:
   |  |  |  |  |  #Rows     = 40
   |  |  |  |  |  Row Width = 48
   |  |  |  |  Piped
   |  |  Sorted Shared Temp Table Completion  ID = t2
   |  |  Access Temp Table  ID = t2
   |  |  |  #Columns = 3
   |  |  |  Relation Scan
   |  |  |  |  Prefetch: Eligible
   |  Insert Into Sorted Shared Temp Table  ID = t3
   |  |  #Columns = 6
   |  |  #Sort Key Columns = 1
   |  |  |  Key 1: (Ascending)
   |  |  Use Partitioned Sort
   |  |  Sortheap Allocation Parameters:
   |  |  |  #Rows     = 44
   |  |  |  Row Width = 76
   |  |  Piped
   |  Access Temp Table  ID = t3
   |  |  #Columns = 6
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
   |  Merge Join
   |  |  Access Table Queue  ID = q5  #Columns = 2
   |  |  Insert Into Sorted Shared Temp Table  ID = t4
   |  |  |  #Columns = 2
   |  |  |  #Sort Key Columns = 1
   |  |  |  |  Key 1: (Ascending)
   |  |  |  Use Partitioned Sort
   |  |  |  Sortheap Allocation Parameters:
   |  |  |  |  #Rows     = 38
   |  |  |  |  Row Width = 28
   |  |  |  Piped
   |  |  Access Temp Table  ID = t4
   |  |  |  #Columns = 2
   |  |  |  Relation Scan
   |  |  |  |  Prefetch: Eligible
   |  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 4 Subagents
   |  Access Table Name = DOOLE.EMPLOYEE  ID = 2,5
   |  |  #Columns = 3
   |  |  Parallel Scan
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
   |  |  Lock Intents
   |  |  |  Table: Intent Share
   |  |  |  Row  : Next Key Share
   |  |  Insert Into Sorted Shared Temp Table  ID = t5
   |  |  |  #Columns = 3
   |  |  |  #Sort Key Columns = 1
   |  |  |  |  Key 1: WORKDEPT (Ascending)
   |  |  |  Use Round-Robin Sort
   |  |  |  Sortheap Allocation Parameters:
   |  |  |  |  #Rows     = 27
   |  |  |  |  Row Width = 32
   |  |  |  Piped
   |  Sorted Shared Temp Table Completion  ID = t5
   |  Access Temp Table  ID = t5
   |  |  #Columns = 3
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
   |  Insert Into Asynchronous Local Table Queue  ID = q4
   Access Local Table Queue  ID = q4  #Columns = 3
   Insert Into Asynchronous Table Queue  ID = q3
   |  Hash to Specific Node
   |  Rows Can Overflow to Temporary Tables
 
Subsection #3:
   Process Using 4 Subagents
   |  Access Table Name = DOOLE.PROJECT  ID = 2,7
   |  |  #Columns = 2
   |  |  Parallel Scan
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
   |  |  Lock Intents
   |  |  |  Table: Intent Share
   |  |  |  Row  : Next Key Share
   |  |  Insert Into Sorted Shared Temp Table  ID = t6
   |  |  |  #Columns = 2
   |  |  |  #Sort Key Columns = 1
   |  |  |  |  Key 1: DEPTNO (Ascending)
   |  |  |  Use Round-Robin Sort
   |  |  |  Sortheap Allocation Parameters:
   |  |  |  |  #Rows     = 38
   |  |  |  |  Row Width = 28
   |  |  |  Piped
   |  Sorted Shared Temp Table Completion  ID = t6
   |  Access Temp Table  ID = t6
   |  |  #Columns = 2
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
   |  Insert Into Asynchronous Local Table Queue  ID = q6
   Access Local Table Queue  ID = q6  #Columns = 2
   Insert Into Asynchronous Table Queue  ID = q5
   |  Hash to Specific Node
   |  Rows Can Overflow to Temporary Tables
End of section
 
 
Optimizer Plan:
 
              RETURN 
              (   1) 
                |    
               BTQ   
              (   2) 
                |    
               LTQ   
              (   3) 
                |    
              MSJOIN 
              (   4) 
             /      \
       TBSCAN         TBSCAN 
       (   5)         (  20) 
         |              |    
        SORT           SORT  
       (   6)         (  21) 
         |              |    
       MSJOIN          DTQ   
       (   7)         (  22) 
      /      \          |    
  TBSCAN    TBSCAN     LTQ  
  (   8)    (  16)    (  23) 
    |         |         |    
   SORT      SORT     TBSCAN 
  (   9)    (  17)    (  24) 
    |         |         |    
   DTQ      TBSCAN     SORT  
  (  10)    (  18)    (  25) 
    |         |         |    
   LTQ    Table:      TBSCAN 
  (  11)  DOOLE       (  26) 
    |     DEPARTMENT    |    
  TBSCAN              Table:  
  (  12)              DOOLE  
    |                 PROJECT 
   SORT  
  (  13) 
    |    
  TBSCAN 
  (  14) 
    |    
 Table:   
 DOOLE   
 EMPLOYEE 

このプランは、例 3: 区画間並行処理による複数区画データベースのプラン にあるプランと似ていますが、 複数のサブエージェントが各サブセクションを実行する点が異なります。 また、各サブセクションの最後に、 ローカル表待ち行列が、すべてのサブエージェントの結果を収集してから、 修飾行が 2 番目の表待ち行列に挿入され、特定のノードでハッシュされる点も異なります。

例 5: 連合データベース・プラン

この例は、例 1: 「非並列」プランと同じ SQL ステートメントを示していますが、 この照会は、連合データベースでコンパイルされたものです。 この例では、表 DEPARTMENT および PROJECT はデータ・ソースに、 表 EMPLOYEE は連合サーバーにあります。

******************** PACKAGE ***************************************
 
Package Name = DOOLE.DYNEXPLN
	Prep Date = 2000/01/03
	Prep Time = 16:29:01
 
	Bind Timestamp = 2000-01-03-16.29.01.479230
 
	Isolation Level          = Cursor Stability
	Blocking                 = Block Unambiguous Cursors
	Query Optimization Class = 5
 
	Partition Parallel       = No
	Intra-Partition Parallel = No
 
	Function Path            = "SYSIBM", "SYSFUN", "DOOLE"
 
-------------------- SECTION ---------------------------------------
Section = 1
 
 
SQL Statement:  
  
  SELECT x.lastname, x.job, y.deptname, y.location, z.projname 
  FROM employee AS x, department AS y, project AS z 
  WHERE x.workdept = y.deptno AND x.workdept = z.deptno AND y.deptno 
          = z.deptno 
Estimated Cost        = 1954
Estimated Cardinality = 100800
 
Distribute Subquery #2
   |  #Columns = 3
   Insert Into Sorted Shared Temp Table  ID = t1
   |  #Columns = 3
   |  #Sort Key Columns = 1
   |  |  Key 1: Remote Query #2, Output Column 1 (Ascending)
   |  Sortheap Allocation Parameters:
   |  |  #Rows     = 1000
   |  |  Row Width = 56
   |  Piped
   Access Temp Table  ID = t1
   |  #Columns = 3
   |  Relation Scan
   |  |  Prefetch: Eligible
   Merge Join
   |  Access Table Name = DOOLE.DEPARTMENT  ID = 2,5
   |  #Columns = 3
   |  Relation Scan
   |  |  Prefetch: Eligible
   |  |  Lock Intents
   |  |  |  Table: Intent Share
   |  |  |  Row  : Next Key Share
   |  |  Insert Into Sorted Temp Table  ID = t2
   |  |  |  #Columns = 3
   |  |  |  #Sort Key Columns = 1
   |  |  |  |  Key 1: WORKDEPT (Ascending)
   |  |  |  Sortheap Allocation Parameters:
   |  |  |  |  #Rows     = 63
   |  |  |  |  Row Width = 32
   |  |  |  Piped
   |  Sorted Temp Table Completion  ID = t2
   |  Access Temp Table  ID = t2
   |  |  #Columns = 3
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
   Merge Join
   |  Distribute Subquery #1
   |  |  #Columns = 2
   |  Insert Into Sorted Temp Table  ID = t3
   |  |  #Columns = 2
   |  |  |  Key 1: Remote Query #1, Output Column 1 (Ascending)
   |  |  Sortheap Allocation Parameters:
   |  |  |  #Rows     = 1000
   |  |  |  Row Width = 36
   |  |  Piped
   |  Access Temp Table  ID = t3
   |  |  #Columns = 2
   |  |  Relation Scan
   |  |  |  Prefetch: Eligible
   Return Data to Application
   |  #Columns = 5
   
   Distributed Subquery #1:
   Server: REMOTE_SAMPLE  (DB2/CS 7.1)
   Subquery SQL Statement:
 	
        SELECT A0."DEPTNO", A0."PROJNAME"
        FROM "DOOLE"."PROJECT" A0
 
   Nicknames Referenced:
      REMOTE.PROJECT  ID = 7  Base = DOOLE.PROJECT
   #Output Columns = 2
 
   Distributed Subquery #2:
   Server: REMOTE_SAMPLE  (DB2/CS 7.1)
   Subquery SQL Statement:
 	
        SELECT A0."DEPTNO", A0."DEPTNAME", A0."LOCATION"
        FROM "DOOLE"."DEPARTMENT" A0
 
   Nicknames Referenced:
      REMOTE.DEPARTMENT  ID = 4  Base = DOOLE.DEPARTMENT
   #Output Columns = 3
 
End of section
 
 
Optimizer Plan:
                RETURN 
                (   1) 
                  |    
                MSJOIN 
                (   2) 
              /       \
       MSJOIN           TBSCAN 
       (   3)           (  13) 
      /      \            |    
  TBSCAN      TBSCAN     SORT
  (   4)      (   9)    (  14)
    |           |         |    
   SORT        SORT     DSBQRY 
  (   5)      (  10)    (  15) 
    |           |         |    
  DSBQRY      TBSCAN   Nickname:
  (   6)      (  11)   REMOTE
    |           |      PROJECT    
 Nickname:    Table:  
 REMOTE       DOOLE
 DEPARTMENT   EMPLOYEE

このプランは、最初の例のプランと全く同じ内容ですが、 2 つの表のデータはデータ・ソースから取られます。 この 2 つの表には、 分散副照会を使用してアクセスします。 この例では、単純にそれらの表のすべての行を選択しています。 データが連合サーバーに戻されると、 そのデータはローカル表から取られたデータと結合させられます。


[ ページのトップ | 前ページ | 次ページ | 目次 | 索引 ]