select * from ( select Casebh, Casedjr,Casedjrxm,Casedjrbm, Casedjrbmmc, Casedjrdw,Casedjrdwmc,caseldhm, Caseldrdh, casedjrq, Caseywly,caseywlx, nvl(CASEPFCS, 0) casepfcs,casesfmyd casemydname,casemyd,casedjclfs, caseywlb,Caseslzx,Casethkssj,Casethjssj,casesfwx from YWCL_CASE a where 1 = 1 and nvl(CASESCBZ, 0) <> '1' and casezt <> '0206' and nvl(casepfcqbz, '0') = '0' and casezt <> '0215' and exists (select zljkywbh from ywgl_zljk where zljkjkjb = '1' and zljkywbh = a.casebh) and not exists (select zljkywbh from ywgl_zljk where zljkjkjb = '2' and zljkywbh = a.casebh) and nvl(casesfypf, '0') = '1' AND CASEDJRQ >= to_date('2017-03-01 00:00:00', 'yyyy-mm-dd HH24:mi:ss') AND CASEDJRQ <= to_date('2017-04-28 23:59:59', 'yyyy-mm-dd HH24:mi:ss') and exists (select 1 from xtgl_unit u where u.unitcode = casedjrdw start with u.unitcode = '244000000' connect by prior u.unitcode = u.unitparentcode) order by dbms_random.value() ) where rownum <='1' 不好意思没放代码 这是代码,数据量在20万左右吧
select Casebh, Casedjr,Casedjrxm,Casedjrbm, Casedjrbmmc, Casedjrdw,Casedjrdwmc,caseldhm, Caseldrdh, casedjrq, Caseywly,caseywlx,
nvl(CASEPFCS, 0) casepfcs,casesfmyd casemydname,casemyd,casedjclfs, caseywlb,Caseslzx,Casethkssj,Casethjssj,casesfwx
from YWCL_CASE a
where 1 = 1
and nvl(CASESCBZ, 0) <> '1'
and casezt <> '0206'
and nvl(casepfcqbz, '0') = '0'
and casezt <> '0215'
and exists (select zljkywbh
from ywgl_zljk
where zljkjkjb = '1'
and zljkywbh = a.casebh)
and not exists
(select zljkywbh
from ywgl_zljk
where zljkjkjb = '2'
and zljkywbh = a.casebh)
and nvl(casesfypf, '0') = '1'
AND CASEDJRQ >= to_date('2017-03-01 00:00:00', 'yyyy-mm-dd HH24:mi:ss')
AND CASEDJRQ <= to_date('2017-04-28 23:59:59', 'yyyy-mm-dd HH24:mi:ss')
and exists
(select 1
from xtgl_unit u
where u.unitcode = casedjrdw
start with u.unitcode = '244000000'
connect by prior u.unitcode = u.unitparentcode)
order by dbms_random.value()
)
where rownum <='1'
不好意思没放代码 这是代码,数据量在20万左右吧
看看他的执行计划。
性能问题应该主要集中在 exists,和not exists