select case
when (enterdate >= to_date('2011-1-1', 'yyyy-MM-dd') AND
enterdate <= to_date('2011-12-31', 'yyyy-MM-dd')) and
(memberorg.entertype >= '111' and memberorg.entertype <= '172') and
status in (4, 5, 6) then
temp.pkid
else
null
end C5
from (select member.pkid,
member.status,
member.entertype,
b.leavetype as ll
from ut_member member
where instr(BELONGDZB, '001.001.032.005.006') > 0) temp
left join UT_MEMBERORGRELA memberorg on temp.pkid = memberorg.mpkid
where not exists
(select 1
from UT_MEMBERORGRELA MEMBERORGRELA
where MEMBERORGRELA.MPkid = memberorg.Mpkid
and memberorg.enterdate < MEMBERORGRELA.Enterdate)我部署在两个数据库上,一个数据库ut_member 数据是4W6左右,UT_MEMBERORGRELA 5.5W,运行只要3秒多点
还有个数据库ut_member 5W6,UT_MEMBERORGRELA 5.7W,但运行要70秒左右。
两个库索引建的一样的。
求教啊。。
when (enterdate >= to_date('2011-1-1', 'yyyy-MM-dd') AND
enterdate <= to_date('2011-12-31', 'yyyy-MM-dd')) and
(memberorg.entertype >= '111' and memberorg.entertype <= '172') and
status in (4, 5, 6) then
temp.pkid
else
null
end C5
from (select member.pkid,
member.status,
member.entertype,
b.leavetype as ll
from ut_member member
where instr(BELONGDZB, '001.001.032.005.006') > 0) temp
left join UT_MEMBERORGRELA memberorg on temp.pkid = memberorg.mpkid
where not exists
(select 1
from UT_MEMBERORGRELA MEMBERORGRELA
where MEMBERORGRELA.MPkid = memberorg.Mpkid
and memberorg.enterdate < MEMBERORGRELA.Enterdate)我部署在两个数据库上,一个数据库ut_member 数据是4W6左右,UT_MEMBERORGRELA 5.5W,运行只要3秒多点
还有个数据库ut_member 5W6,UT_MEMBERORGRELA 5.7W,但运行要70秒左右。
两个库索引建的一样的。
求教啊。。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货