10g full join 最佳化

今天一個女生諮詢我報名學最佳化。聊著聊著就讓我最佳化一個sql，貼給大家看一下

select (case         when grouping(allwo.workshop_code) = 1 then          ''         else          nvl(max(allwo.workshop_code), '未維護車間')       end) workshop_code,       DECODE(TO_NUMBER(substr(Item_code,                               4,                               INSTR(Item_code, 'M', 1, 2) - 4)) / 10,              NULL,              '合計',              TO_NUMBER(substr(Item_code, 4, INSTR(Item_code, 'M', 1, 2) - 4)) / 10 || 'M') xiangsu,       (case         when grouping(item_code) = 1 then          ''         else          max(part_spec)       end) part_spec,       (case         when grouping(item_code) = 1 then          ''         else          max(type)       end) type,       nvl(wo_code, '') wo_code,       sum(qty) qty,       sum(UsedQty) qx,       sum(db) db,       sum(wb) wb,       sum(HM) HM,       sum(成品站) 成品站,       sum(烘烤) 烘烤,       sum(十級產出) 十級產出,       sum(分粒接收) 分粒接收,       sum(分粒修邊) 分粒修邊,       sum(焊接) 焊接,       sum(噴碼打標) 噴碼打標,       sum(撕膜) 撕膜,       sum(調焦) 調焦,       sum(點膠) 點膠,       sum(點膠全檢) 點膠全檢,       sum(功測一) 功測一,       sum(功測接收修複) 功測接收修複,       sum(功測二) 功測二,       sum(QC功測) QC功測,       sum(遠焦燒錄) 遠焦燒錄,       sum(近焦燒錄) 近焦燒錄,       sum(OTP燒錄) OTP燒錄,       sum(OTP檢測) OTP檢測,       sum(QCOTP燒錄) QCOTP燒錄,       sum(背膠) 背膠,       sum(外觀) 外觀,       sum(OS測試) OS測試,       sum(CCD檢驗) CCD檢驗,       sum(貼膜) 貼膜,       sum(封裝) 封裝,       sum(封裝入庫) 封裝入庫,       sum(修複接收) 修複接收,       sum(修複維修) 修複維修,       sum(髒點測試) 髒點測試,       sum(修複焊接) 修複焊接,       sum(修複修邊) 修複修邊,       sum(修複調焦) 修複調焦,       sum(修複功能測試) 修複功能測試,       sum(修複轉出) 修複轉出  from (select COALESCE(wocode,                        gxcl.wo_code,                        bz.wo_code,                        xiufujieshou.wo_code) wocode,               nvl(UsedQty, 0) UsedQty,               nvl(db, 0) db,               nvl(wb, 0) wb,               nvl(成品站, 0) 成品站,               nvl(HM, 0) HM,               nvl(烘烤, 0) 烘烤,               nvl(十級產出, 0) 十級產出,               nvl(分粒接收, 0) 分粒接收,               nvl(分粒修邊, 0) 分粒修邊,               nvl(焊接, 0) 焊接,               nvl(噴碼打標, 0) 噴碼打標,               nvl(撕膜, 0) 撕膜,               nvl(調焦, 0) 調焦,               nvl(點膠, 0) 點膠,               nvl(點膠全檢, 0) 點膠全檢,               nvl(功測一, 0) 功測一,               nvl(功測接收修複, 0) 功測接收修複,               nvl(功測二, 0) 功測二,               nvl(QC功測, 0) QC功測,               nvl(遠焦燒錄, 0) 遠焦燒錄,               nvl(近焦燒錄, 0) 近焦燒錄,               nvl(OTP燒錄, 0) OTP燒錄,               nvl(OTP檢測, 0) OTP檢測,               nvl(QCOTP燒錄, 0) QCOTP燒錄,               nvl(背膠, 0) 背膠,               nvl(外觀, 0) 外觀,               nvl(OS測試, 0) OS測試,               nvl(CCD檢驗, 0) CCD檢驗,               nvl(貼膜, 0) 貼膜,               nvl(修複接收, 0) 修複接收,               nvl(修複維修, 0) 修複維修,               nvl(髒點測試, 0) 髒點測試,               nvl(修複焊接, 0) 修複焊接,               nvl(修複修邊, 0) 修複修邊,               nvl(修複調焦, 0) 修複調焦,               nvl(修複功能測試, 0) 修複功能測試,               nvl(修複轉出, 0) 修複轉出,               nvl(封裝入庫, 0) 封裝入庫,               nvl(封裝, 0) 封裝          from (SELECT WO_CODE AS WoCode, sum(USED_QTY) AS UsedQty                  FROM T_SN2UPN                 where (mat_code like 'MFY%' OR mat_code like 'EMBY%')                   and MDATE >=                       to_date('2014-04-27 08:30', 'yyyy-mm-dd hh24:mi:ss')                   AND MDATE <=                       to_date('2014-04-28 08:30', 'yyyy-mm-dd hh24:mi:ss')                   and SUBSTR(TO_CHAR(MDATE + 30 / 1440, 'YYYYMMDDHH24MISS'),                              9,                              2) >= 9                   AND SUBSTR(TO_CHAR(MDATE + 30 / 1440, 'YYYYMMDDHH24MISS'),                              9,                              2) < 21                 group by Wo_Code) qx          full join (select A.item_code,                           A.wo_code,                           C.PIXEL,                           SUM(case                                 when a.op_code = 'DB' THEN                                  OK_QTY                                 ELSE                                  0                               END) DB,                           SUM(case                                 when a.op_code = 'WB後全檢' THEN                                  OK_QTY                                 ELSE                                  0                               END) WB,                           SUM(case                                 when a.op_code in                                      ('半成品組裝', '組裝LENS', 'VCM封合') THEN                                  OK_QTY                                 ELSE                                  0                               END) 成品站,                           SUM(case                                 when a.op_code = 'H/M' THEN                                  OK_QTY                                 ELSE                                  0                               END) HM,                           SUM(case                                 when a.op_code = '烘烤' THEN                                  OK_QTY                                 ELSE                                  0                               END) 烘烤,                           SUM(case                                 when a.op_code = '十級轉出' THEN                                  OK_QTY                                 ELSE                                  0                               END) 十級產出,                           SUM(case                                 when a.op_code = '分粒接收' THEN                                  OK_QTY                                 ELSE                                  0                               END) 分粒接收,                           SUM(case                                 when a.op_code = '修邊' THEN                                  OK_QTY                                 ELSE                                  0                               END) 分粒修邊,                           SUM(case                                 when a.op_code = '馬達焊接' THEN                                  OK_QTY                                 ELSE                                  0                               END) 焊接,                           SUM(case                                 when a.op_code = '打標' THEN                                  OK_QTY                                 ELSE                                  0                               END) 噴碼打標,                           SUM(case                                 when a.op_code = '撕膜' THEN                                  OK_QTY                                 ELSE                                  0                               END) 撕膜,                           SUM(case                                 when a.op_code = '調焦' THEN                                  OK_QTY                                 ELSE                                  0                               END) 調焦,                           SUM(case                                 when a.op_code = '點螺紋膠及UV固化' THEN                                  OK_QTY                                 ELSE                                  0                               END) 點膠,                           SUM(case                                 when a.op_code = '點UV膠後全檢膠量' THEN                                  OK_QTY                                 ELSE                                  0                               END) 點膠全檢,                           SUM(case                                 when a.op_code = '功能測試' THEN                                  OK_QTY                                 ELSE                                  0                               END) 功測一,                           SUM(case                                 when a.op_code = '功能測試接收(修複)' THEN                                  OK_QTY                                 ELSE                                  0                               END) 功測接收修複,                           SUM(case                                 when a.op_code = '功能測試02' THEN                                  OK_QTY                                 ELSE                                  0                               END) 功測二,                           SUM(case                                 when a.op_code = 'QC功能檢' THEN                                  OK_QTY                                 ELSE                                  0                               END) QC功測,                           SUM(case                                 when a.op_code = '遠焦燒錄' THEN                                  OK_QTY                                 ELSE                                  0                               END) 遠焦燒錄,                           SUM(case                                 when a.op_code = '近焦燒錄' THEN                                  OK_QTY                                 ELSE                                  0                               END) 近焦燒錄,                           SUM(case                                 when a.op_code = 'OTP燒錄' THEN                                  OK_QTY                                 ELSE                                  0                               END) OTP燒錄,                           SUM(case                                 when a.op_code = 'OTP檢測' THEN                                  OK_QTY                                 ELSE                                  0                               END) OTP檢測,                           SUM(case                                 when a.op_code = 'QC OTP檢測' THEN                                  OK_QTY                                 ELSE                                  0                               END) QCOTP燒錄,                           SUM(case                                 when a.op_code = '貼背膠' THEN                                  OK_QTY                                 ELSE                                  0                               END) 背膠,                           SUM(case                                 when a.op_code = '外觀檢查' THEN                                  OK_QTY                                 ELSE                                  0                               END) 外觀,                           SUM(case                                 when a.op_code = 'O/S測試' THEN                                  OK_QTY                                 ELSE                                  0                               END) OS測試,                           SUM(case                                 when a.op_code = 'CCD檢驗' THEN                                  OK_QTY                                 ELSE                                  0                               END) CCD檢驗,                           SUM(case                                 when a.op_code = '貼膜' THEN                                  OK_QTY                                 ELSE                                  0                               END) 貼膜,                           SUM(case                                 when a.op_code = '修複維修' THEN                                  OK_QTY                                 ELSE                                  0                               END) 修複維修,                           SUM(case                                 when a.op_code = '髒點測試' THEN                                  OK_QTY                                 ELSE                                  0                               END) 髒點測試,                           SUM(case                                 when a.op_code = '修複焊接' THEN                                  OK_QTY                                 ELSE                                  0                               END) 修複焊接,                           SUM(case                                 when a.op_code = '修複修邊' THEN                                  OK_QTY                                 ELSE                                  0                               END) 修複修邊,                           SUM(case                                 when a.op_code = '修複調焦' THEN                                  OK_QTY                                 ELSE                                  0                               END) 修複調焦,                           SUM(case                                 when a.op_code = '修複功能測試' THEN                                  OK_QTY                                 ELSE                                  0                               END) 修複功能測試,                           SUM(case                                 when a.op_code = '修複功能測試' THEN                                  OK_QTY                                 ELSE                                  0                               END) 修複轉出                      from t_resume a, i_material c                     WHERE a.item_code = c.part_code                       AND (a.rcard, a.op_code, a.actionseq) in                           (select rcard, op_code, max(actionseq) actionseq                              from t_resume                             where MDATE >=                                   to_date('2014-04-27 08:30',                                           'yyyy-mm-dd hh24:mi:ss')                               AND MDATE <=                                   to_date('2014-04-28 08:30',                                           'yyyy-mm-dd hh24:mi:ss')                             group by rcard, op_code)                       AND A.WO_CODE = 'MN1-14040158'                                         GROUP BY A.ITEM_CODE, A.WO_CODE, C.PIXEL                                        ) gxcl            on qx.WoCode = gxcl.wo_code                  full join (select substr(rcard, 1, 12) wo_code,                           sum(case                                 when (IS_PACKING = 1 and                                      a.PACKING_DATE >=                                      to_date('2014-04-27 08:30',                                               'yyyy-mm-dd hh24:mi:ss') AND                                      a.PACKING_DATE <=                                      to_date('2014-04-28 08:30',                                               'yyyy-mm-dd hh24:mi:ss')) and                                      SUBSTR(TO_CHAR(a.PACKING_DATE + 30 / 1440,                                                     'YYYYMMDDHH24MISS'),                                             9,                                             2) >= 9 AND                                      SUBSTR(TO_CHAR(PACKING_DATE + 30 / 1440,                                                     'YYYYMMDDHH24MISS'),                                             9,                                             2) < 21 then                                  used_qty                                 else                                  0                               end) 封裝,                           sum(case                                 when (IS_BZTOSTOCK = 1 and                                      a.BZTOSTOCK_DATE >=                                      to_date('2014-04-27 08:30',                                               'yyyy-mm-dd hh24:mi:ss') AND                                      a.BZTOSTOCK_DATE <=                                      to_date('2014-04-28 08:30',                                               'yyyy-mm-dd hh24:mi:ss')) and                                      SUBSTR(TO_CHAR(a.BZTOSTOCK_DATE + 30 / 1440,                                                     'YYYYMMDDHH24MISS'),                                             9,                                             2) >= 9 AND                                      SUBSTR(TO_CHAR(BZTOSTOCK_DATE + 30 / 1440,                                                     'YYYYMMDDHH24MISS'),                                             9,                                             2) < 21 then                                  used_qty                                 else                                  0                               end) 封裝入庫                      from (select *                              from T_CARTON                             where status IS NULL                               and workshop_code = '生產二區') a                     inner join t_carton2SN b                        on a.carton_no = b.carton_no                       AND ((a.BZTOSTOCK_DATE >=                           to_date('2014-04-27 08:30',                                     'yyyy-mm-dd hh24:mi:ss') AND                           a.BZTOSTOCK_DATE <=                           to_date('2014-04-28 08:30',                                     'yyyy-mm-dd hh24:mi:ss')) OR                           (a.PACKING_DATE >=                           to_date('2014-04-27 08:30',                                     'yyyy-mm-dd hh24:mi:ss') AND                           a.PACKING_DATE <=                           to_date('2014-04-28 08:30',                                     'yyyy-mm-dd hh24:mi:ss')))                     group by substr(rcard, 1, 12)) bz            on gxcl.wo_code = bz.wo_code                  full join                 (select wo_code, sum(ng_qty) 修複接收           from QTMR01          where MDATE >=                to_date('2014-04-27 08:30', 'yyyy-mm-dd hh24:mi:ss')            AND MDATE <=                to_date('2014-04-28 08:30', 'yyyy-mm-dd hh24:mi:ss')            and SUBSTR(TO_CHAR(MDATE + 30 / 1440, 'YYYYMMDDHH24MISS'), 9, 2) >= 9            AND SUBSTR(TO_CHAR(MDATE + 30 / 1440, 'YYYYMMDDHH24MISS'), 9, 2) < 21          group by wo_code) xiufujieshou            on xiufujieshou.wo_code = gxcl.wo_code) jieguo inner join i_wo allwo    on allwo.wo_code = jieguo.wocode inner join i_material M    on m.part_code = allwo.item_code where 1 = 1   and allwo.workshop_code = '生產二區' group by rollup(item_code, allwo.workshop_code, wo_code)

該sql是 olap 的， 在oracle10g 上面跑。跑一次要33秒鐘，一般olap報表，最好的客戶體驗要在5秒以內。

大家注意看，sql裡面有full join，在oracle10g裡面，full join預設會掃描2次，一次left join 一次right join 然後union all. 這樣肯定慢了。在11g 只掃描1次。

10g 裡面可以用 一個hint  /*+ NATIVE_FULL_OUTER_JOIN */ ，讓他們掃描1次

加了hint之後，SQL 3秒能出結果，她還問能否進一步最佳化，因為要講課了，8點半講課，沒啥時間，明天幫忙看吧。

注意，這個hint別放在 第一個select 後面，這樣是沒用的。要放在 from (select /*+ NATIVE_FULL_OUTER_JOIN */  COALESCE(wocode,

也就是說要離full join 最近的一個 select 。至於為什麼，請自己 google query block 關鍵字， hint一般只在 query block 生效。