db2expln ¹× dynexplnÀ¸·ÎºÎÅÍ Á¦°øµÈ Ãâ·ÂÀÇ ¹èÄ¡¿Í Çü½ÄÀ» ÀÌÇØÇÏ´Â µ¥ µµ¿òÀÌ µÇ´Â 5°¡ÁöÀÇ ¿¹°¡ ´ÙÀ½¿¡ ³ª¿É´Ï´Ù. ÀÌ ¿¹µéÀº DB2¿¡¼ Á¦°øµÇ´Â SAMPLE µ¥ÀÌÅͺ£À̽º¿¡ ´ëÇØ ¼öÇàµÈ °ÍÀÔ´Ï´Ù. °¢ ¿¹¿¡ ´ëÇØ °£´ÜÇÑ ¼³¸íÀÌ ³ª¿É´Ï´Ù. °¢ ¿¹°£ÀÇ Â÷ÀÌÁ¡Àº ±½ÀºÃ¼·Î Ç¥½ÃµË´Ï´Ù.
ÀÌ ¿¹´Â Á÷¿øÀÇ À̸§, ¾÷¹«, ºÎ¼ À̸§ ¹× À§Ä¡¿Í À̵éÀÌ ÀÏÇϰí ÀÖ´Â ÇÁ·ÎÁ§Æ® À̸§À» ¿äûÇÏ´Â ´Ü¼øÇÑ °ÍÀÔ´Ï´Ù. ÀÌ ¾×¼¼½º Ç÷£ÀÇ ÇÙ½ÉÀº º´ÇÕ Á¶ÀÎÀÌ ÁöÁ¤µÈ °¢ Å×ÀÌºí¿¡¼ ÀÖ´Â °ü·Ã µ¥ÀÌÅÍ¿Í Á¶ÀÎÇÏ´Â µ¥ »ç¿ëµÈ´Ù´Â °ÍÀÔ´Ï´Ù. »öÀÎÀ» »ç¿ëÇÒ ¼ö ¾ø±â ¶§¹®¿¡, ¾×¼¼½º Ç÷£Àº °¢ Å×ÀÌºí¿¡ ´ëÇÑ °ü°è ½ºÄµÀ» ¼öÇàÇÏ¸ç °¢ Å×À̺íÀº Á¶ÀÎÇϱâ Àü¿¡ Á¤·ÄµÇ¾î¾ß ÇÕ´Ï´Ù.
******************** 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 Å×ÀÌºí¿¡ Á¶Àε˴ϴÙ. °á°ú ÇàÀº ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ¸®Åϵ˴ϴÙ.
ÀÌ ¿¹¿¡¼´Â ¿¹ 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°£ÀÇ º´ÇÕ Á¶ÀÎÀ» ó¸®ÇÏ´Â ¼ºê¿¡ÀÌÀüÆ®°¡ ¼ø¼¸¦ ¹þ¾î³ Á¶ÀÎµÈ ÇàÀ» »ý¼ºÇÒ ¼ö Àֱ⠶§¹®¿¡ ÇÊ¿äÇÕ´Ï´Ù.
ÀÌ ¿¹¿¡¼´Â ¿¹ 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
ÀÌ Ç÷£Àº ù¹øÂ° ¿¹¿Í °°Áö¸¸, ¼½¼ÇÀÌ ³× °³ÀÇ ºÎ¼Ó¼½¼ÇÀ¸·Î ³ª´©¾îÁ® ÀÖ½À´Ï´Ù. ºÎ¼Ó¼½¼ÇÀº ´ÙÀ½ Ÿ½ºÅ©¸¦ Æ÷ÇÔÇÕ´Ï´Ù.
ÀÌ ¿¹¿¡¼´Â ¿¹ 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â Àü¿¡ ¸ðµç ¼ºê¿¡ÀÌÀüÆ®·ÎºÎÅÍ °á°ú¸¦ ¼öÁýÇÕ´Ï´Ù.
ÀÌ ¿¹¿¡¼´Â ¿¹ 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
ÀÌ Ç÷£Àº ù¹øÂ° ¿¹¿¡¼ÀÇ Ç÷£°ú °°Áö¸¸, Å×À̺í Áß µÎ °³¿¡ ´ëÇÑ µ¥ÀÌÅͰ¡ µ¥ÀÌÅÍ ¼Ò½º·ÎºÎÅÍ Á¦°øµÈ´Ù´Â Á¡Àº ¿¹¿ÜÀÔ´Ï´Ù. µÎ °³ÀÇ Å×À̺íÀº ºÐ»ê ºÎ¼Ó Á¶È¸¸¦ ÅëÇØ ¾×¼¼½ºµÇ´Âµ¥, ÀÌ °æ¿ì °£´ÜÇÏ°Ô ±× Å×ÀÌºí¿¡¼ ¸ðµç ÇàÀ» ¼±ÅÃÇÕ´Ï´Ù. µ¥ÀÌÅͰ¡ ¿¬ÇÕ ¼¹ö·Î ¸®ÅϵǸé, ±× µ¥ÀÌÅÍ´Â Áö¿ª Å×À̺íÀÇ µ¥ÀÌÅÍ¿¡ Á¶Àε˴ϴÙ.