我的执行计划：如图。
sql语句：select distinct t.fpatientid pId,
card.fcardno,
t.fcardseqno cardSeqNo,
t.fpatientno mzPNo,
pinfo.fname name,
hisbase.GetHzPYCap(nvl(pinfo.fname, ' ')) PY,
pinfo.fsex sex,
case
when pinfo.fbirthday is not null then
case
when floor((sysdate - pinfo.fbirthday) / 365.25) >= 18 then
floor((sysdate - pinfo.fbirthday) / 365.25) || '岁'
else
case
when floor((sysdate - pinfo.fbirthday) / 365.25) = 1 then
floor((sysdate - pinfo.fbirthday) / 365.25) || '岁' ||
(floor((sysdate - pinfo.fbirthday) / 365.25 * 12) -
floor((sysdate - pinfo.fbirthday) / 365.25) * 12) || '月'
else
floor((sysdate - pinfo.fbirthday) / 365.25 * 12) || '月' ||
floor(floor((sysdate - pinfo.fbirthday) / 365.25 * 12 *
30.4375) -
floor((sysdate - pinfo.fbirthday) / 365.25 * 12) *
30.4375) || '天'
end
end
else
pinfo.fage
end age,
t.flbh lbh,
t.freglsh regMzlsh,
t.fregno regNo,
to_char(rp.frpdate, 'YYYY-MM-DD HH24:MI:SS') examTime,
t.fpatienttype patientType,
t.fstate examState,
t.fkh kh,
k.fks ks,
t.fysh ysh,
p.fname ys,
1 dataSouce,
pinfo.FTELNUMBER ftele,
pinfo.FHOMEPLACE faddr,
t.faccountid,
0 ftid,
0 QueNo
from mzdoctor.Tpatient t
Left Join hisbase.tkssz k
On t.fkh = k.fkh
Left Join hisbase.tperson p
On t.fysh = p.ftybh
Left Join Mzcard.tpersoninfo pinfo
on t.FACCOUNTID = pinfo.FACCOUNTID
Left Join Mzcard.tcardinfo card
on t.FACCOUNTID = card.FACCOUNTID
left join mzdoctor.tprpmain rp
on t.fpatientid = rp.fpid
left join mzdoctor.tprpgrp rpgrp
on rp.frpid = rpgrp.frpid
left join hisbase.tdrugmode m
on rpgrp.fdrugmodecode = m.fdrugmodecode
left join mzdoctor.tprpdetail rpmx
on rp.frpid = rpmx.frpid
left join hisbase.tdrugcode d
on rpmx.fitemcode = d.fmcode
and rpmx.fitemflag = 0
where 1 = 1
and rp.frpdate > sysdate - 3 * 1440 / (24 * 60)
and (m.fisinjection = 1)
and (rp.Ffeestate = 2)
order by examTime desc
nested loops outer 太耗资源，我想改成hash join。请问怎么来修改？
或者哪位能给点优化的建议。
|
