小白求指导 sql 优化

Question

1、Patient.FAlternateNo like '%' 这个没啥意义吧，去掉
2、ORDER BY 'PATNAME';order by一个字符串，没啥意义吧，去掉了
SELECT
    Patient.fid,
    Patient.fName_l1,
    Patient.FAlternateNo,
    Patient.FAlternateNo2,
    Patient.FCoSchemeCode,
    Company.FName_l1,
    Patient.FHKID,
    Patient.FPatientACNo,
    OwnerPat.FName_L1 as acctName,
    Patient.FStaffGrade,
    Contract.FPlanDescription,
    Patient.FStatus
FROM
T_UF_Patient Patient
LEFT JOIN T_BD_Company Company ON Patient.FCoCode = Company.FNumber
LEFT JOIN (
    select * from T_CM_ContractMain T
    where not exists (select 1 from t_cm_contractmain
    where NVL(fversionnumber,0)> NVL(T.fversionnumber,0)
    and fnumber = T.fnumber
    and fid=T.FID)
) Contract ON Patient.FCoSchemeCode = Contract.FNumber
LEFT JOIN T_UF_Patient OwnerPat on Patient.FCOSchemeCode = OwnerPat.FCOSchemeCode
                               and Patient.FPatientDependACNO  = OwnerPat.FPatientACNo

Answer 1

其实我初始版本是： 
SELECT
    Patient.fid,
    Patient.fName_l1,
    Patient.FAlternateNo,
    Patient.FAlternateNo2,
    Patient.FCoSchemeCode,
    Company.FName_l1,
    Patient.FHKID,
    Patient.FPatientACNo,
    OwnerPat.FName_L1 as acctName,
    Patient.FStaffGrade,
    Contract.FPlanDescription,
    Patient.FStatus
　FROM
　  T_UF_Patient Patient
    LEFT JOIN T_BD_Company Company ON Patient.FCoCode = Company.FNumber
    LEFT JOIN T_CM_ContractMain Contract ON Patient.FCoSchemeCode = Contract.FNumber
    LEFT JOIN T_UF_Patient OwnerPat on Patient.FCOSchemeCode = OwnerPat.FCOSchemeCode and Patient.FPatientDependACNO  = OwnerPat.FPatientACNo
  WHERE
  (Patient.FAlternateNo like '<ern>%' or Patient.FHKID = '<hkid>')
  and Contract.FID in (
    SELECT distinct c1.fid FROM t_cm_contractmain c1,
    (SELECT max(fversionnumber) fversionnumber,fnumber FROM t_cm_contractmain GROUP BY fnumber) c2
    WHERE NVL(c1.fversionnumber,0) = NVL(c2.fversionnumber,0) and c1.fnumber = c2.fnumber
  )
  ORDER BY $(sortCriteria) 

Answer 2

第一个left join 改为 join，你原来在where里对left join右侧的表限定条件，left join就没有效果了，改为join试试

Answer 3

另外把条件    and fid=T.FID去掉，这个貌似是多余的

Answer 4

也就是说fid不是你那个表的关键字呗
JOIN (
select * from T_CM_ContractMain T1
WHERE EXISTS (
    select * from T_CM_ContractMain T
    where not exists (select 1 from t_cm_contractmain
    where NVL(fversionnumber,0)> NVL(T.fversionnumber,0)
    and fnumber = T.fnumber)
and fid=T1.FID)
) Contract

Answer 5

结果对了， 不过查询了106多秒  和原来的差不多了，请问大神还可以优化哪里么，现在我的 sql 语句是SELECT
    Count(*)
FROM
T_UF_Patient Patient
LEFT JOIN T_BD_Company Company ON Patient.FCoCode = Company.FNumber
JOIN (
select * from T_CM_ContractMain T1
WHERE EXISTS (
    select * from T_CM_ContractMain T
    where not exists (select 1 from t_cm_contractmain
    where NVL(fversionnumber,0)> NVL(T.fversionnumber,0)
    and fnumber = T.fnumber)
and fid=T1.FID)
) Contract ON Patient.FCoSchemeCode = Contract.FNumber
LEFT JOIN T_UF_Patient OwnerPat on Patient.FCOSchemeCode = OwnerPat.FCOSchemeCode
                               and Patient.FPatientDependACNO  = OwnerPat.FPatientACNo
原始版本是： SELECT
    Count(*)
FROM
  T_UF_Patient Patient
    LEFT JOIN T_BD_Company Company ON Patient.FCoCode = Company.FNumber
    LEFT JOIN T_CM_ContractMain Contract ON Patient.FCoSchemeCode = Contract.FNumber
    LEFT JOIN T_UF_Patient OwnerPat on Patient.FCOSchemeCode = OwnerPat.FCOSchemeCode and Patient.FPatientDependACNO  = OwnerPat.FPatientACNo
  WHERE
  Contract.FID in (
    SELECT distinct c1.fid FROM t_cm_contractmain c1,
    (SELECT max(fversionnumber) fversionnumber,fnumber FROM t_cm_contractmain GROUP BY fnumber) c2
    WHERE NVL(c1.fversionnumber,0) = NVL(c2.fversionnumber,0) and c1.fnumber = c2.fnumber
  );

Answer 6

JOIN (
SELECT * FROM (
SELECT T.*,MIN(RN)OVER(PARTITION BY FID) M FROM(
select T_CM_ContractMain.*,ROW_NUMBER()OVER(PARTITION BY fnumber ORDER BY fversionnumber DESC) RN from T_CM_ContractMain
     ) T
)WHERE M=1
) Contract
至少确定慢的原因是这了，改为分析函数的写法试试
不行的话只能靠增加索引的方式提高速度了
增加fnumber、 fversionnumber、FID的组合索引

Answer 7

没啥不好，就是版本太低(oracle9i以下)用不了

Answer 8

假如 T_CM_ContractMain 下，(FNumber,fversionnumber) 唯一，并且 (FNumber,FID) 也唯一。
你的意图是每个FNumber只需要最新（fversionnumber最大）一条的 FID 是不是？
可以用 OUTER APPLY，从#10原始版本改的。
SELECT
    Count(*)
FROM
  T_UF_Patient Patient
    LEFT JOIN T_BD_Company Company ON Patient.FCoCode = Company.FNumber
    LEFT JOIN T_UF_Patient OwnerPat on Patient.FCOSchemeCode = OwnerPat.FCOSchemeCode and Patient.FPatientDependACNO  = OwnerPat.FPatientACNo
    OUTER APPLY (SELECT TOP 1 *
                   FROM T_CM_ContractMain
                  WHERE FNumber = Patient.FCoSchemeCode
               ORDER BY fversionnumber DESC
                ) Contract