°ü¸® ¾È³»¼­


db2expln ¹× dynexpln Ãâ·ÂÀÇ ¿¹

db2expln ¹× dynexplnÀ¸·ÎºÎÅÍ Á¦°øµÈ Ãâ·ÂÀÇ ¹èÄ¡¿Í Çü½ÄÀ» ÀÌÇØÇÏ´Â µ¥ µµ¿òÀÌ µÇ´Â 5°¡ÁöÀÇ ¿¹°¡ ´ÙÀ½¿¡ ³ª¿É´Ï´Ù. ÀÌ ¿¹µéÀº DB2¿¡¼­ Á¦°øµÇ´Â SAMPLE µ¥ÀÌÅͺ£À̽º¿¡ ´ëÇØ ¼öÇàµÈ °ÍÀÔ´Ï´Ù. °¢ ¿¹¿¡ ´ëÇØ °£´ÜÇÑ ¼³¸íÀÌ ³ª¿É´Ï´Ù. °¢ ¿¹°£ÀÇ Â÷ÀÌÁ¡Àº ±½ÀºÃ¼·Î Ç¥½ÃµË´Ï´Ù.

¿¹ 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.deptno
 
 
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 ¹æÇâ 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
 

ÀÌ Ç÷£Àº ù¹øÂ° ¿¹ÀÇ Ç÷£°ú °ÅÀÇ °°½À´Ï´Ù. ÁÖ¿ä Â÷ÀÌÁ¡Àº Ç÷£ÀÌ Ã³À½À¸·Î ½ÃÀÛµÉ ¶§ ³× °³ÀÇ ¼­ºê¿¡ÀÌÀüÆ®°¡ ÀÛ¼ºµÇ°í, Ç÷£ÀÌ ³¡³¯ ¶§ Å×ÀÌºí ´ë±âÇà·ÄÀÌ °¢ ¼­ºê¿¡ÀÌÀüÆ®ÀÇ ÀÛ¾÷ °á°ú¸¦ ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ¸®ÅϵDZâ Àü¿¡ ¼öÁýÇÑ´Ù´Â °ÍÀÔ´Ï´Ù.

EMPLOYEE¸¦ Á¶ÀÎÇϱâ Àü¿¡ Ãß°¡·Î Á¤·ÄÀÌ ÇÊ¿äÇÏ´Ù´Â °Íµµ ¾Ë¾ÆµÎ½Ê½Ã¿À. ÀÌ´Â DEPARTMENT¿Í PROJECT°£ÀÇ º´ÇÕ Á¶ÀÎÀ» ó¸®ÇÏ´Â ¼­ºê¿¡ÀÌÀüÆ®°¡ ¼ø¼­¸¦ ¹þ¾î³­ Á¶ÀÎµÈ ÇàÀ» »ý¼ºÇÒ ¼ö Àֱ⠶§¹®¿¡ ÇÊ¿äÇÕ´Ï´Ù.

¿¹ 3: ÆÄƼ¼Ç°£ º´·Ä 󸮸¦ »ç¿ëÇÑ ´ÙÁß ÆÄƼ¼Ç µ¥ÀÌÅͺ£À̽º Ç÷£

ÀÌ ¿¹¿¡¼­´Â ¿¹ 1: º´·Ä 󸮰¡ ¾ø´Â Ç÷£°ú µ¿ÀÏÇÑ SQL¹®À» º¸¿©ÁÖÁö¸¸, ÀÌ Á¶È¸´Â ¼¼ °³ÀÇ µ¥ÀÌÅͺ£À̽º ÆÄƼ¼ÇÀ¸·Î ±¸¼ºµÈ ÆÄƼ¼ÇµÈ µ¥ÀÌÅͺ£À̽º¿¡¼­ ÄÄÆÄÀϵǾú½À´Ï´Ù.

******************** 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: ÆÄƼ¼Ç°£ ¹× ÆÄƼ¼Ç ³» º´·Ä 󸮸¦ »ç¿ëÇÑ ´ÙÁß ÆÄƼ¼Ç µ¥ÀÌÅͺ£À̽º Ç÷£

ÀÌ ¿¹¿¡¼­´Â ¿¹ 1: º´·Ä 󸮰¡ ¾ø´Â Ç÷£°ú µ¿ÀÏÇÑ SQL¹®À» º¸¿© ÁÖÁö¸¸, ÀÌ Á¶È¸´Â °¢°¢ 4¹æÇâ 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: ÆÄƼ¼Ç°£ º´·Ä 󸮸¦ »ç¿ëÇÑ ´ÙÁß ÆÄƼ¼Ç µ¥ÀÌÅͺ£À̽º Ç÷£ÀÇ Ç÷£°ú À¯»çÇÕ´Ï´Ù. ¶ÇÇÑ, °¢ ºÎ¼Ó¼½¼ÇÀÇ ³¡¿¡¼­ Áö¿ª Å×ÀÌºí ´ë±âÇà·ÄÀº ±ÔÁ¤ÇÏ´Â ÇàÀÌ Æ¯Á¤ ³ëµå·Î ÇØ½¬µÉ µÎ¹øÂ° Å×ÀÌºí ´ë±âÇà·Ä¿¡ »ðÀԵDZâ Àü¿¡ ¸ðµç ¼­ºê¿¡ÀÌÀüÆ®·ÎºÎÅÍ °á°ú¸¦ ¼öÁýÇÕ´Ï´Ù.

¿¹ 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

ÀÌ Ç÷£Àº ù¹øÂ° ¿¹¿¡¼­ÀÇ Ç÷£°ú °°Áö¸¸, Å×À̺í Áß µÎ °³¿¡ ´ëÇÑ µ¥ÀÌÅͰ¡ µ¥ÀÌÅÍ ¼Ò½º·ÎºÎÅÍ Á¦°øµÈ´Ù´Â Á¡Àº ¿¹¿ÜÀÔ´Ï´Ù. µÎ °³ÀÇ Å×À̺íÀº ºÐ»ê ºÎ¼Ó Á¶È¸¸¦ ÅëÇØ ¾×¼¼½ºµÇ´Âµ¥, ÀÌ °æ¿ì °£´ÜÇÏ°Ô ±× Å×ÀÌºí¿¡¼­ ¸ðµç ÇàÀ» ¼±ÅÃÇÕ´Ï´Ù. µ¥ÀÌÅͰ¡ ¿¬ÇÕ ¼­¹ö·Î ¸®ÅϵǸé, ±× µ¥ÀÌÅÍ´Â Áö¿ª Å×À̺íÀÇ µ¥ÀÌÅÍ¿¡ Á¶Àε˴ϴÙ.


[ ÆäÀÌÁöÀÇ ¸Ç À§ | ÀÌÀü ÆäÀÌÁö | ´ÙÀ½ ÆäÀÌÁö | ¸ñÂ÷ | »öÀÎ ]