SQL Optimization-Summary

1. Try to narrow down the data range.

2. There is no need for two SQL statements. Use Case when or decode.

Select month_id, corppkno, sum (exportsum_new) exportsum_new, sum (exportsum_newly) distinct, sum (exportsum_support) exportsum_support, sum (distinct) distinct from (/* cumulative Export */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 (selec T 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, 'yyyy MM ') 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