一个执行47小时、耗尽CPU的SQL优化

uang520 · 发表于 2014-3-7 13:16

我转换了一下SQL，和上面的逻辑是一样的。楼主可以把SQL的执行计划贴出来：
select
m.mkt_terminal_sale_interface_id,
iv.billtypename,
c.customer_name,
mt.terminal_id terminal_id,
(select dm.dictname
from cpcdict dm
where lower(dm.dictcode) like 'terminal_level%'
   and dm.dictvalue = mt.terminal_level
   and dm.entid = 2750) Terminal_Level,
(select ds.dictname
from cpcdict ds
where lower(ds.dictcode) like 'terminal_property%'
   and ds.dictvalue = mt.terminal_property
   and ds.entid = 2750) Terminal_Property,
eh.employee_code employee_code,
d.dept_code dept_code,
dd.attribute1 Big_District,
io.specs item_spec,
i.item_id,
(select icd.item_class_name
from item_class icd
where io.item_class2 = icd.item_class_id
   and icd.organization_id = 2750) Item_Class2_Name,
(select icx.item_class_name
from item_class icx
where io.item_class1 = icx.item_class_id
   and icx.organization_id = 2750) Item_Class1_Name,
(select dt.dictname
from cpcdict dt
where lower(dt.dictcode) like 'capacity%'
   and dt.dictvalue = io.capacity
   and dt.entid = 2750) Capacity,
ib1.brand_username brand_sub,
ib2.brand_username brand_series
  from
  (select * from mkt_terminal_sale_interface m
where m.report_time >=o_date('2013-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and m.report_time <= to_date('2013-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and m.billtypecode in ('1001', '1002', '1009')
and nvl(m.is_gift, 0) <> 2
And exists (Select 1
      From Sa_Deptaccessctrl Sd
      Where Sd.Orgid = mt.org_id
         and Sd.Userid = 'kfo_???'
         and Sd.Organization_Id = 2750)
         and 2750 = m.organization_id) m,
   item_org                   io,
   item                      i,
   employee_header          eh,
   customer                   c,
   inv_billtype             iv,
   mkt_terminal             mt,
   dept                      d,
   mkt_ccs_ims_centre       dd,
   item_brand_attribute       ib1,
   item_brand_attribute       ib2
where m.item_id = i.item_id
and m.employee_id = eh.employee_id
and m.billtypecode = iv.billtypecode(+)
and m.customer_id = c.customer_id(+)
and io.item_id = m.item_id
and 2 = nvl(eh.is_guider, 2)
and 2750 = io.organization_id
and io.item_id = i.item_id
and io.brand_sub = ib1.brand_id(+)
and io.brand_series = ib2.brand_id(+)
and io.organization_id = m.organization_id
and (2 > nvl(eh.Engage_Type, 0) or 2 < nvl(eh.Engage_Type, 0))
and eh.crm_entid = io.crm_entid
and m.terminal_id = mt.terminal_id(+)
and mt.organization_id = 2750
and mt.org_id = d.dept_id(+)
and d.entid = 2750
and mt.org_id = dd.dept_id(+)
and mt.crm_entid = io.crm_entid
and nvl(mt.applies_terminal, 0) = 2
and eh.organization_id = 2750