SQL Optimization-Summary

SQL Optimization-Conclusion 1: Minimize the data scope. 2. There is no need for two SQL statements. Use case when or decode.

[SQL] select month_id, corppkno, sum (exportSum_new) exportSum_new, sum (exportSum_newLy) exportSum_newLy, sum (exportSum_Support) exportSum_Support, sum (exportSum_SupportLy) exportSum_SupportLy from (/* cumulative export in the current year */select. month_id, c. corppkno, decode (. isnewinsurant, null, 0, B. exportdollar) exportSum_new,/* here to use an SQL statement */0 exportSum_newLy, B. exportdollar exportSum_Support, 0 exportSum_SupportLy from (Select trunc (t1.month _ id/100) yearid, t1.month _ id, t3.cocode, max (t. newinsurantpkno_sm) isnewinsurant/* new customers in the current month */from stdw. f_Sum_SupportInsurant_SM t, stdw. lu_month_cumulate t1, stdw. d_t_customer t2, stdw. d_t_Customsenterprisemapping t3 where t. monthid = t1.month _ cumul_id and t. supportinsuantpkno_sm = t2.pkno and t2.crmno = t3.customno and t3.state = '1' and t1.month _ id <= to_char (sysdate-1, 'Yyyymm') group by t1.month _ id, t3.cocode) A, stdw. f_custom_company_composite B, stdw. d_custom_branch_province C, stdw. lu_month_cumulate D/* This SQL statement first uses subquery A to limit the range, and then uses A to associate B. Because B has A large scope, if B is summarized and then associated with a, the efficiency is low */where B. monthid = d. month_cumul_id and B. corpid = c. corpid and. yearid = B. yearid/* cross-region access */and. month_id = d. month_id and. cocode = B. cocode union all/* total export volume of the previous year */select. month_id, B. corppkno, 0 exportSum_new, decode (. isnewinsurant, null, 0, B. exportdollar) exportSum_newLy, 0 exportSum_Support, B. exportdollar exportSum_SupportLy from (select trunc (t1.month _ id/100)-1 yearid_ly, t1.month _ id, t3.cocode, max (t. newinsurantpkno_sm) isnewinsurant/* new customers in the current month */from stdw. f_Sum_SupportInsurant_SM t, stdw. lu_month_cumulate t1, stdw. d_t_customer t2, stdw. d_t_Customsenterprisemapping t3 where t. monthid = t1.month _ cumul_id and t. region = t2.pkno and t2.crmno = t3.customno and t3.state = '1' and t1.month _ id <= to_char (sysdate-1, 'yyymmm ') group by t1.month _ id, t3.cocode), (select t1.outputyear yearid, t1.cocode, t4.corppkno, t1.totaldollar exportdollar from stdw. f_custom_company_total t1, stdw. d_custom_company t2, stdw. d_custom_province_zone t3, stdw. d_custom_branch_province t4 where t1.cocode = t2.cocode and t2.zonecode = t3.zone and t3.province _ no = t4.proviceid) B where. yearid_ly = B. yearid and. cocode = B. cocode) group by month_id, corppkno