10g full join Optimization

Today, a girl asked me to register for optimization. After chatting, let me optimize an SQL statement and paste it for you to see.

Select (case when grouping (allwo. workshop_code) = 1 then'' else nvl (max (allwo. workshop_code), 'workshop not maintained ') end) workshop_code, DECODE (TO_NUMBER (substr (Item_code, 4, INSTR (Item_code, 'M', 1, 2)-4 )) /10, NULL, 'Total', 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 (finished product station) finished product station, sum (baking) baking, sum (ten-level output) Ten-level output, sum (receive by grain), sum (repair by grain), sum (welding), sum (marking by injection code), sum (Tearing film, sum (focusing) focusing, sum (dispensing) dispensing, sum (dispensing) dispensing, sum (Power Test 1) Power Test 1, sum (Power Test receive repair) acceptance and repair of power test, sum (Power Test 2) Power Test 2, sum (QC power test) QC power test, sum (far focus burning) far focus burning, sum (near-focus burning) near-focus burning, sum (OTP burning) OTP burning, sum (OTP detection) OTP detection, sum (QCOTP burning) QCOTP burning, sum (Back gum) Back gum, sum (appearance) appearance, sum (OS test) OS test, sum (CCD test) CCD test, sum (film) film, sum (packaging) packaging, sum (packaging) packaging warehouse receiving, sum (repair receipt) repair receipt, sum (repair) repair, sum (dirty point test) Dirty point test, sum (Repair Welding) repair welding, sum (repair side repair) repair side repair, sum (repair focus) repair focus, sum (repair function test) repair function test, sum (repair transfer out) repair 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 (finished station, 0) finished station, nvl (HM, 0) HM, nvl (baking, 0) baking, nvl (ten-level output, 0) Ten-level output, nvl (receiving by grain, 0) receiving by grain, nvl (grain trimming, 0) grain trimming, nvl (welding, 0) welding, nvl (injection marking, 0) injection marking, nvl (tear film, 0) tear film, nvl (focusing, 0) focusing, nvl (dispensing, 0) dispensing, nvl (dispensing, 0) dispensing, nvl (Power Test 1, 0) Power Test 1, nvl (Power Test acceptance repair, 0) Power Test acceptance repair, nvl (Power Test 2, 0) Power Test 2, nvl (QC power test, 0) QC power test, nvl (Remote focus burning, 0) Remote focus burning, nvl (near focus burning, 0) near focus burning, nvl (OTP burning, 0) OTP burning, nvl (OTP detection, 0) OTP detection, nvl (QCOTP burning, 0) QCOTP burning, nvl (adhesive, 0) adhesive, nvl (appearance, 0) appearance, nvl (OS test, 0) OS test, nvl (CCD test, 0) CCD test, nvl (film, 0) film, nvl (repair receipt, 0) repair receipt, nvl (repair, 0) repair and repair, nvl (dirty point test, 0) Dirty point test, nvl (repair welding, 0) repair welding, nvl (repair side, 0) repair side repair, nvl (repair focus, 0) repair focus, nvl (repair function test, 0) repair function test, nvl (repair transfer out, 0) repair transfer out, nvl (packaging warehouse, 0) packaging warehouse, nvl (packaging, 0) packaging 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 ('2017-04-27 ', 'yyyy-mm-dd hh24: mi: ss ') and mdate <= to_date ('2017-04-28 ', 'yyyy-mm-dd hh24: mi: ss') and SUBSTR (TO_CHAR (MDATE + 2014, 'yyyymmddhh24mis'), 9, 2)> = 9 and substr (TO_CHAR (MDATE + 30/1440, 'yyyymmddhh24mis'), 9, 2) <21 group by Wo_Code) qx full join (select. item_code,. wo_code, C. PIXEL, SUM (case when. op_code = 'db' THEN OK _QTY ELSE 0 END) DB, SUM (case when. op_code = 'wb after full check' THEN OK _QTY ELSE 0 END) WB, SUM (case when. op_code in ('semi-finished Assembly ', 'Assemble lens', 'vcm Block') THEN OK _QTY ELSE 0 END) finished station, SUM (case when. op_code = 'H/m' THEN OK _QTY ELSE 0 END) HM, SUM (case when. op_code = 'baking 'then OK _QTY ELSE 0 END) baking, SUM (case when. op_code = 'Ten-level transfer output' THEN OK _QTY ELSE 0 END) Ten-level output, SUM (case when. op_code = 'split-receiving 'then OK _QTY ELSE 0 END) Split-receiving, SUM (case when. op_code = 'shout' THEN OK _QTY ELSE 0 END) Split edge repair, SUM (case when. op_code = 'Motor solder' THEN OK _QTY ELSE 0 END) welding, SUM (case when. op_code = 'tag' THEN OK _QTY ELSE 0 END) injection marking, SUM (case when. op_code = 'Tear file' THEN OK _QTY ELSE 0 END), SUM (case when. op_code = 'Focusing THEN OK _QTY ELSE 0 END) focusing, SUM (case when. op_code = 'point threaded rubber and UV-curing 'then OK _QTY ELSE 0 END), SUM (case when. op_code = 'total amount of glue inspected after clicking uv' THEN OK _QTY ELSE 0 END) Total amount of glue inspected, SUM (case when. op_code = 'function test' THEN OK _QTY ELSE 0 END) power test1, SUM (case when. op_code = 'function test receipt (repair) 'THEN OK _QTY ELSE 0 END) power test receipt repair, SUM (case when. op_code = 'function test 02' THEN OK _QTY ELSE 0 END) Power Test 2, SUM (case when. op_code = 'qc function check' THEN OK _QTY ELSE 0 END) QC power test, SUM (case when. op_code = 'far from burning records' THEN OK _QTY ELSE 0 END) far from burning records, SUM (case when. op_code = 'near-focus burning records' THEN OK _QTY ELSE 0 END) near-focus burning records, SUM (case when. op_code = 'otp burning records' THEN OK _QTY ELSE 0 END) OTP burning, SUM (case when. op_code = 'otp check' THEN OK _QTY ELSE 0 END) OTP checksum (case when. op_code = 'qc OTP detection 'then OK _QTY ELSE 0 END) QCOTP burning, SUM (case when. op_code = 'adhesive tape THEN OK _QTY ELSE 0 END) adhesive tape, SUM (case when. op_code = 'appearance check 'then OK _QTY ELSE 0 END) appearance, SUM (case when. op_code = 'o/S test 'then OK _QTY ELSE 0 END) OS test, SUM (case when. op_code = 'ccd test 'then OK _QTY ELSE 0 END) CCD test, SUM (case when. op_code = 'Film THEN OK _QTY ELSE 0 END) film, SUM (case when. op_code = 'repair repair' THEN OK _QTY ELSE 0 END) repair, SUM (case when. op_code = 'dirty test 'then OK _QTY ELSE 0 END) Dirty test, SUM (case when. op_code = 'fixed solder' THEN OK _QTY ELSE 0 END) fixed solder', SUM (case when. op_code = 'repair edge THEN OK _QTY ELSE 0 END) repair edge repair, SUM (case when. op_code = 'fixed focusing THEN OK _QTY ELSE 0 END) fixed focusing, SUM (case when. op_code = 'repair function test' THEN OK _QTY ELSE 0 END) repair function testing, SUM (case when. op_code = 'repair function test' THEN OK _QTY ELSE 0 END) fix transfer from t_resume a, I _material c WHERE. item_code = c. part_code AND (. rcard,. op_code,. actionseq) in (select rcard, op_code, max (actionseq) actionseq from t_resume where MDATE> = to_date ('2017-04-27 ', 'yyyy-mm-dd hh24: mi: ss') and mdate <= to_date ('2017-04-28 ', 'yyyy-mm-dd hh24: mi: ss') group by rcard, op_code) and. WO_CODE = 'mn1-14040158 'group by. ITEM_CODE,. 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. PACKING_DATE> = to_date ('2017-04-27 ', 'yyyy-mm-dd hh24: mi: ss') AND. PACKING_DATE <= to_date ('2017-04-28 ', 'yyyy-mm-dd hh24: mi: ss') and SUBSTR (TO_CHAR (. 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) packaging, sum (case when (IS_BZTOSTOCK = 1 and. BZTOSTOCK_DATE> = to_date ('2017-04-27 ', 'yyyy-mm-dd hh24: mi: ss') AND. BZTOSTOCK_DATE <= to_date ('2017-04-28 ', 'yyyy-mm-dd hh24: mi: ss') and SUBSTR (TO_CHAR (. 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 = 'production two Region') a inner join t_carton2SN B on. carton_no = B. carton_no AND (. BZTOSTOCK_DATE> = to_date ('2017-04-27 ', 'yyyy-mm-dd hh24: mi: ss') AND. BZTOSTOCK_DATE <= to_date ('2017-04-28 ', 'yyyy-mm-dd hh24: mi: ss') OR (. PACKING_DATE> = to_date ('2017-04-27 ', 'yyyy-mm-dd hh24: mi: ss') AND. PACKING_DATE <= to_date ('2017-04-28 ', '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) fix received from QTMR01 where MDATE> = to_date ('2017-04-27 ', 'yyyy-mm-dd hh24: mi: ss ') and mdate <= to_date ('2017-04-28', 'yyyy-mm-dd hh24: mi: ss') and SUBSTR (TO_CHAR (MDATE + 2014, 'hangzhou'), 9, 2)> = 9 and substr (TO_CHAR (MDATE + 30/1440, 'yyyymmddhh24mis'), 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 = 'production two region' group by rollup (item_code, allwo. workshop_code, wo_code)

This SQL statement is olap and runs on oracle10g. It takes 33 seconds to run each time. Generally, olap reports require the best customer experience within 5 seconds.

Note that there is full join in the SQL statement. In oracle10g, full join scans twice by default. One left join, one right join, and then union all. This is definitely slow. Scan only once at 11g.

You can use a hint/* + NATIVE_FULL_OUTER_JOIN */In the 10 Gb to let them scan once.

After the hint is added, the SQL statement can produce results in 3 seconds. She also asked if further optimization is possible, because it is necessary to give lectures at half past eight, so there is no time to give lectures. Let's see it tomorrow.

Note: This hint should not be placed behind the first select statement, which is useless. To be placed in from (select/* + NATIVE_FULL_OUTER_JOIN */COALESCE (wocode,

That is, a select statement closest to full join. For the reason, please google the query block keyword. The hint generally takes effect only in the query block.

 

Zookeeper