如何提高select 语句的执行速度

congfu

select distinct H."HOSPITALIZENO",H."NAME",H."SEX",H."BIRTHDAY",H."REQUESTPHYSICIAN",H."STUDYDEPT",H."FEE",H."HISTYPE",H."STUDYPART",H."POSTCODE",H."REQUESTID",H."HISRECORDID",H."STUDYDATE",H."MODALITY",H."EXE_DEPT_CODE",H."INSURANCEID",H."ADDRESS",H."TELNO",H."BEDNO",H."DEPCODE",H."SBLXX10" from (
select substr(nvl(b.scfxx02,a.Smzjs01),2)||a.ijsmx01 as HospitalizeNo,
       b.sbrxx02 as Name,
       decode(b.sbrxx03,'AD01','M','AD02','F','O') AS Sex,
       b.dbrxx05 as Birthday,
       (select szgxx02 from yygl170 where szgxx01 = a.Sjsmx11) as RequestPhysician,
       (select sksxx02 from yygl140 where sksxx01 = a.Sjsmx10) as studydept,
       a.Njsmx06 as Fee,
       ' ' as histype,
       ' ' as Studypart,
       '' as Postcode,
      '' as RequestID,
       a.Smzjs01 as hisrecordid,
       Djsmx14 as Studydate,
       ' ' as Modality,
         a.Sjsmx12 as exe_dept_code,
       b.scfxx13 as insuranceid,
         b.Sbrxx12 as address,
      '' as telno,
      '' as BedNo,
       a.Sjsmx12 as depcode,
       b.Sblxx10 as sblxx10
from (select * from yygle41 where sjsmx12 in  ('08','25','41')
and djsmx14  >trunc(add_months(sysdate,-3))
and smzjs01 not in  (select smzjs01 from yygle40
  where (smzjs11=1 or (nmzjs06<0 and smzjs12 is not null))and dmzjs13>=trunc(add_months(sysdate,-3)))
) a,(select * from yygle61 where dcfxx04 >trunc(add_months(sysdate,-3))) b
where a.scfxx01 = b.scfxx01
and a.Sjsmx09 like 'BD02%'
and b.dcfxx04 >trunc(add_months(sysdate,-3))
and a.sjsmx12 in  ('08','25','41')
UNION ALL
select substr(nvl(b.smzjs30,b.smzjs01),2)||a.ijsmx01 as HospitalizeNo,
       b.smzjs02 as Name,
       (select decode(sghjl04,'AD01','M','AD02','F','O') from yygle10 where sghjl01=b.smzjs30) AS Sex,
       (select Dghjl05 from yygle10 where sghjl01=b.smzjs30)  as Birthday,
       (select szgxx02 from yygl170 where szgxx01 = a.Sjsmx11) as RequestPhysician,
       (select sksxx02 from yygl140 where sksxx01 = a.Sjsmx10) as studydept,
       a.Njsmx06 as Fee,
        ' ' as histype,
       ' ' as Studypart,
       '' as Postcode,
      '' as RequestID,
        a.Smzjs01 as hisrecordid,
       a.Djsmx14 as Studydate,
       ' ' as Modality,
         a.Sjsmx12 as exe_dept_code,
       '' as insuranceid,
         '' as address,
      '' as telno,
      '' as BedNo,
       a.Sjsmx12 as depcode,
        (select nvl(Sblxx10,'') from yygle61 where a.scfxx01 = scfxx01)  as sblxx10
from yygle41 a,yygle40 b where
a.smzjs01=b.smzjs01 and
a.djsmx14 >=trunc(add_months(sysdate,-3))
and b.smzjs01 not in  (select smzjs01 from yygle40
  where (smzjs11=1 or (nmzjs06<0 and smzjs12 is not null))and b.dmzjs13>=trunc(add_months(sysdate,-3)))
and a.sjsmx12 in ('08','25','41')) H
where h.hisrecordid not in (select ssqdh01 from pacs_his where dsysdate >=trunc(sysdate))
       --and h.HospitalizeNo not in (select szybh01 from pacs_his where dsysdate >=trunc(sysdate))
order by H.HospitalizeNo;
备注：
yygle40有5万多条记录，yygle41有10万多条记录，yygle61有4万多条记录，并且都在以每天近千条记录的速度在增加。

doni

数据量不大,但逻辑太复杂,而且在SQL中又用到not in等低效率的因素
SQL写成这样对于维护,优化是很不利的
对我来说,我是不允许有这样的SQL存在的
建议SQL拆分或重构

congfu

多谢   指点