sort merge join导致temp被爆菊

原创 2013年12月04日 17:29:54
SQL_ID  cqsz37256v36j, child number 1
-------------------------------------
INSERT /*+append*/ INTO TMP_ACCT_AF NOLOGGING ( DATA_DATE , ACCT_NO , ACCT_ORD , ACCT_NO_PK , ACCT_BAL , D_CMP_BAL ,
M_CMP_BAL , Y_CMP_BAL , FLAG , ACCT_FLAG , TERM , TERM_FLAG , CUR_CODE , CUR_NAME , SUB_CODE , CUST_NO , CUST_TYPE ,
CUST_NAME , BANK_CORP_CODE , BRAN_NAME , MGR_CODE , MGR_NAME , OPEN_DATE , FIX_BAL , DIV_FIX_FLAG , ADJUST_AMT ,
ADJUST_AMT_AF , Y_AVG_AF , Y_ADD_AF , ACCT_INTR , SIM_PROFIT , SEPA_POR , PRI , BRAN_CODE , UNIT1_CODE , UNIT2_CODE ,
UNIT3_CODE , DEPT1_CODE , INTR_RATE , DUE_DATE ) SELECT /*+use_merge(t1 t2)parallel(t1 16) use_hash(t1 t3)*/ DATA_DATE,
ACCT_NO, ACCT_ORD, ACCT_NO_PK, ACCT_BAL, D_CMP_BAL, M_CMP_BAL, Y_CMP_BAL, FLAG, ACCT_FLAG, TERM, TERM_FLAG, CUR_CODE,
CUR_NAME, SUB_CODE, CUST_NO, CUST_TYPE, CUST_NAME, BANK_CORP_CODE, BRAN_NAME, NVL(T3.MGR_CODE, T1.MGR_CODE), T1.MGR_NAME,
OPEN_DATE, FIX_BAL, DIV_FIX_FLAG, ADJUST_AMT, ADJUST_AMT_AF, Y_AVG_AF, Y_ADD_AF, ACCT_INTR, SIM_PROFIT, SEPA_POR, PRI,
T1.BRAN_CODE, T2.UNIT1_CODE, T2.UNIT2_CODE, T1.BRAN_CODE
 
Plan hash value: 1366440900
 
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |                    |       |       |    24 (100)|          |        |      |            |
|   1 |  LOAD AS SELECT                  |                    |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR                 |                    |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)           | :TQ10005           |     1 |   672 |    24  (30)| 00:00:01 |  Q1,05 | P->S | QC (RAND)  |
|   4 |     VIEW                         |                    |     1 |   672 |    24  (30)| 00:00:01 |  Q1,05 | PCWP |            |
|   5 |      SORT UNIQUE                 |                    |     1 |   650 |    24  (30)| 00:00:01 |  Q1,05 | PCWP |            |
|   6 |       PX RECEIVE                 |                    |     1 |   650 |    23  (27)| 00:00:01 |  Q1,05 | PCWP |            |
|   7 |        PX SEND HASH              | :TQ10004           |     1 |   650 |    23  (27)| 00:00:01 |  Q1,04 | P->P | HASH       |
|*  8 |         HASH JOIN OUTER          |                    |     1 |   650 |    23  (27)| 00:00:01 |  Q1,04 | PCWP |            |
|   9 |          PX RECEIVE              |                    |     1 |   622 |    18  (28)| 00:00:01 |  Q1,04 | PCWP |            |
|  10 |           PX SEND HASH           | :TQ10003           |     1 |   622 |    18  (28)| 00:00:01 |  Q1,03 | P->P | HASH       |
|  11 |            MERGE JOIN OUTER      |                    |     1 |   622 |    18  (28)| 00:00:01 |  Q1,03 | PCWP |            |
|  12 |             SORT JOIN            |                    |     1 |   601 |     3  (34)| 00:00:01 |  Q1,03 | PCWP |            |
|  13 |              PX RECEIVE          |                    |     1 |   601 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |
|  14 |               PX SEND HASH       | :TQ10002           |     1 |   601 |     2   (0)| 00:00:01 |  Q1,02 | P->P | HASH       |
|  15 |                PX BLOCK ITERATOR |                    |     1 |   601 |     2   (0)| 00:00:01 |  Q1,02 | PCWC |            |
|* 16 |                 TABLE ACCESS FULL| TMP_ACCT_AF2       |     1 |   601 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|* 17 |             SORT JOIN            |                    |  8158 |   167K|    15  (27)| 00:00:01 |  Q1,03 | PCWP |            |
|  18 |              BUFFER SORT         |                    |       |       |            |          |  Q1,03 | PCWC |            |
|  19 |               PX RECEIVE         |                    |  8158 |   167K|    12   (9)| 00:00:01 |  Q1,03 | PCWP |            |
|  20 |                PX SEND HASH      | :TQ10000           |  8158 |   167K|    12   (9)| 00:00:01 |        | S->P | HASH       |
|  21 |                 TABLE ACCESS FULL| S_PM_MGR_DEPT_RELA |  8158 |   167K|    12   (9)| 00:00:01 |        |      |            |
|  22 |          BUFFER SORT             |                    |       |       |            |          |  Q1,04 | PCWC |            |
|  23 |           PX RECEIVE             |                    |  3902 |   106K|     5  (20)| 00:00:01 |  Q1,04 | PCWP |            |
|  24 |            PX SEND HASH          | :TQ10001           |  3902 |   106K|     5  (20)| 00:00:01 |        | S->P | HASH       |
|* 25 |             INDEX FAST FULL SCAN | MGR_DEPT_RELA_IDX2 |  3902 |   106K|     5  (20)| 00:00:01 |        |      |            |
------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   8 - access("T1"."MGR_CODE"="T3"."MGR_CODE")
  16 - access(:Z>=:Z AND :Z<=:Z)
  17 - access("T1"."BRAN_CODE"="T2"."UNIT3_CODE")
       filter("T1"."BRAN_CODE"="T2"."UNIT3_CODE")
  25 - filter("T3"."DEPT1_CODE"<>'999999999')


 TMP_ACCT_AF2 T1

(SELECT DISTINCT T2.UNIT3_CODE, T2.UNIT2_CODE, T2.UNIT1_CODE
                 FROM S_PM_MGR_DEPT_RELA T2) T2


S_PM_MGR_DEPT_RELA T3

 

T1 和 T2 进行 sort merge join 在 T1和 T3进行 hash join,直接导致temp被耗尽

 

 (SELECT /*+use_merge(t1 t2)parallel(t1 16) use_hash(t1 t3)*/
   DATA_DATE,
   ACCT_NO,
   ACCT_ORD,
   ACCT_NO_PK,
   ACCT_BAL,
   D_CMP_BAL,
   M_CMP_BAL,
   Y_CMP_BAL,
   FLAG,
   ACCT_FLAG,
   TERM,
   TERM_FLAG,
   CUR_CODE,
   CUR_NAME,
   SUB_CODE,
   CUST_NO,
   CUST_TYPE,
   CUST_NAME,
   BANK_CORP_CODE,
   BRAN_NAME,
   NVL(T3.MGR_CODE, T1.MGR_CODE),
   T1.MGR_NAME,
   OPEN_DATE,
   FIX_BAL,
   DIV_FIX_FLAG,
   ADJUST_AMT,
   ADJUST_AMT_AF,
   Y_AVG_AF,
   Y_ADD_AF,
   ACCT_INTR,
   SIM_PROFIT,
   SEPA_POR,
   PRI,
   T1.BRAN_CODE,
   T2.UNIT1_CODE,
   T2.UNIT2_CODE,
   T1.BRAN_CODE AS UNIT3_CODE,
   NVL(T3.DEPT1_CODE, '999999999'),
   T1.INTR_RATE,
   T1.DUE_DATE
    FROM TMP_ACCT_AF2 T1
    LEFT JOIN S_PM_MGR_DEPT_RELA T3
      ON T1.MGR_CODE = T3.MGR_CODE
     AND T3.DEPT1_CODE <> '999999999'
    LEFT JOIN (SELECT DISTINCT T2.UNIT3_CODE, T2.UNIT2_CODE, T2.UNIT1_CODE
                 FROM S_PM_MGR_DEPT_RELA T2) T2
      ON T1.BRAN_CODE = T2.UNIT3_CODE)

Plan hash value: 4109009912
 
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT              |                    |   142M|    28G|       |   629  (69)| 00:00:02 |        |      |            |
|   1 |  LOAD AS SELECT               | TMP_ACCT_AF        |       |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR              |                    |       |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)        | :TQ10002           |   142M|    28G|       |   629  (69)| 00:00:02 |  Q1,02 | P->S | QC (RAND)  |
|   4 |     MERGE JOIN OUTER          |                    |   142M|    28G|       |   629  (69)| 00:00:02 |  Q1,02 | PCWP |            |
|   5 |      SORT JOIN                |                    |  1500K|   208M|   450M|   254  (27)| 00:00:01 |  Q1,02 | PCWP |            |
|*  6 |       HASH JOIN RIGHT OUTER   |                    |  1500K|   208M|       |   225  (17)| 00:00:01 |  Q1,02 | PCWP |            |
|   7 |        BUFFER SORT            |                    |       |       |       |            |          |  Q1,02 | PCWC |            |
|   8 |         PX RECEIVE            |                    |  3902 | 62432 |       |     5  (20)| 00:00:01 |  Q1,02 | PCWP |            |
|   9 |          PX SEND BROADCAST    | :TQ10000           |  3902 | 62432 |       |     5  (20)| 00:00:01 |        | S->P | BROADCAST  |
|* 10 |           INDEX FAST FULL SCAN| MGR_DEPT_RELA_IDX2 |  3902 | 62432 |       |     5  (20)| 00:00:01 |        |      |            |
|  11 |        PX BLOCK ITERATOR      |                    |  1407K|   174M|       |   217  (16)| 00:00:01 |  Q1,02 | PCWC |            |
|  12 |         TABLE ACCESS FULL     | TMP_ACCT_AF2       |  1407K|   174M|       |   217  (16)| 00:00:01 |  Q1,02 | PCWP |            |
|* 13 |      SORT JOIN                |                    |  7315 |   471K|       |    17  (36)| 00:00:01 |  Q1,02 | PCWP |            |
|  14 |       BUFFER SORT             |                    |       |       |       |            |          |  Q1,02 | PCWC |            |
|  15 |        PX RECEIVE             |                    |  7315 |   471K|       |    15  (27)| 00:00:01 |  Q1,02 | PCWP |            |
|  16 |         PX SEND BROADCAST     | :TQ10001           |  7315 |   471K|       |    15  (27)| 00:00:01 |        | S->P | BROADCAST  |
|  17 |          VIEW                 |                    |  7315 |   471K|       |    15  (27)| 00:00:01 |        |      |            |
|  18 |           SORT UNIQUE         |                    |  7315 |   150K|       |    15  (27)| 00:00:01 |        |      |            |
|  19 |            TABLE ACCESS FULL  | S_PM_MGR_DEPT_RELA |  8158 |   167K|       |    12   (9)| 00:00:01 |        |      |            |
-----------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("T1"."MGR_CODE"="T3"."MGR_CODE"(+))
  10 - filter("T3"."DEPT1_CODE"(+)<>'999999999')
  13 - access("T1"."BRAN_CODE"="T2"."UNIT3_CODE"(+))
       filter("T1"."BRAN_CODE"="T2"."UNIT3_CODE"(+))

 正确关联顺序为T1 和 T3进行HASH JOIN  在通过T1和T2 进行sort merge join

Mugeda(木疙瘩)H5案例课—拍拍员工被玩坏了

-
  • 1970年01月01日 08:00

深入理解Oracle表(3):三大表连接方式详解之Nested loop join和 Sort merge join

关系数据库技术的精髓就是通过关系表进行规范化的数据存储       并通过各种表连接技术和各种类型的索引技术来进行信息的检索和处理       这里Think愿意和大家一起来学习分享Oracle的三大...
  • linwaterbin
  • linwaterbin
  • 2013-01-28 00:33:15
  • 5012

排序合并连接(sort merge join)的原理

排序合并连接(sort merge join)     访问次数:两张表都只会访问0次或1次。     驱动表是否有顺序:无。     是否要排序:是。     应用场景:当结果集已经排过序。 排序...
  • guogang83
  • guogang83
  • 2013-11-01 08:24:09
  • 3647

12 排序合并连接(SORT MERGE JOIN)--优化主题系列

排序合并连接(SORT MERGE JOIN) 前文提到嵌套循环以及哈希连接,他们都有驱动表概念。排序合并连接没有驱动表一说,两个表/行源是对等关系。排序合并连接原理是先对两个表/行源根据JOIN...
  • leo0805
  • leo0805
  • 2017-10-09 10:30:32
  • 403

Oracle三种表连接方式netsed loop、merge sort join、hash join

1 nested loop join 循环嵌套连接:行源1的每一条记录,依次去匹配行源2的每条记录,将符合连接条件的记录放在结果集中,直到行源1的所有记录都完成这个操作。循环嵌套连接是最基本也是最古...
  • panzhaobo775
  • panzhaobo775
  • 2012-03-07 10:58:01
  • 6861

Hive Sort Merge Bucket Map Join(大表关联)

测试:一个4000万和一个5000多万的表Join,关联键数据倾斜,并且笛卡尔积,效果明显。建立小表create table lxw_test1(id int,name string,date_tim...
  • yjgithub
  • yjgithub
  • 2017-03-27 16:21:46
  • 779

Hive Sort Merge Bucket Map Join

测试:一个4000万和一个5000多万的表Join,关联键数据倾斜,并且笛卡尔积,效果明显。 #建立小表 create table lxw_test1(id int,name string...
  • lzm1340458776
  • lzm1340458776
  • 2015-01-29 18:20:53
  • 1610

优酷KUX视频批量爆菊

  • 2017年09月12日 14:42
  • 238KB
  • 下载

Sort-Merge Joins(归并连接算法)

看到一篇较好的归并连接算法描述,是我苦苦寻找的文章,转载之。 Sort-Merge Joins       As we have seen from the discussion...
  • fz2543122681
  • fz2543122681
  • 2017-08-07 21:37:16
  • 614

普通表的Join 三种算法(join 一) 嵌套循环Join(Nested Loops Join)、排序合并Join(Sort-Merge Join)和哈希Join(Hash Join)

refer to http://mysun.iteye.com/blog/1748473 通过Map-Reduce实现Join系列之一 本系列的开篇在提到使用Map-Reduce实现Join之前,...
  • G7N3F
  • G7N3F
  • 2016-01-18 16:58:32
  • 2181
收藏助手
不良信息举报
您举报文章:sort merge join导致temp被爆菊
举报原因:
原因补充:

(最多只允许输入30个字)