有没有哪个大神能帮我优化一下这个 oracle sql，多余的字段我删除了，只有待优化的语句。

[Dragon_In_Sky]

 

pharmacy.drug_stock  150000个

comm.drug_price_list 40000个

其余表中不超过1000个数量 

select 
       (select amount_per_package
          from comm.drug_price_list
         where drug_code = d.drug_code
           and min_spec = d.drug_spec
           and drug_spec <> d.drug_spec
           and firm_id = d.firm_id
           and min_units = d.units
           and units <> d.units
           and start_date <= sysdate
           and (stop_date >= sysdate or stop_date is null)
           and rownum <= 1) as quantityPerPackage,
       (select units
          from comm.drug_price_list
         where drug_code = d.drug_code
           and min_spec = d.drug_spec
           and drug_spec <> d.drug_spec
           and firm_id = d.firm_id
           and min_units = d.units
           and units <> d.units
           and start_date <= sysdate
           and (stop_date >= sysdate or stop_date is null)
           and rownum <= 1) as packageUnitCode, 
       (select units
          from comm.drug_price_list
         where drug_code = d.drug_code
           and min_spec = d.drug_spec
           and drug_spec <> d.drug_spec
           and firm_id = d.firm_id
           and min_units = d.units
           and units <> d.units
           and start_date <= sysdate
           and (stop_date >= sysdate or stop_date is null)
           and rownum <= 1) as packageUnit, 
       c.quantity -
       (select nvl(sum(db.quantity), 0)
          from drug_presc_master_temp da, drug_presc_detail_temp db
         where da.presc_date = db.presc_date
           and da.presc_no = db.presc_no
           and db.drug_code = c.drug_code
           and db.drug_spec = c.drug_spec
           and db.firm_id = c.firm_id
           and db.package_units = c.units
           and da.dispensary = c.storage
           and db.quantity > 0) as storeNum, 
  from comm.drug_dict             a,
       comm.drug_name_dict        b,
       comm.drug_price_list       d,
       pharmacy.drug_storage_dept e,
       pharmacy.drug_kssxx        g,
       pharmacy.drug_jbyw         h,
       pharmacy.drug_same_yp      i,
       pharmacy.drug_stock        c,
       pharmacy.kss_drug_ps       f
 where a.drug_code = b.drug_code
   and a.drug_code = c.drug_code
   and a.drug_spec = c.drug_spec
   and a.units = c.units
   and a.drug_code = d.drug_code
   and c.package_spec = d.drug_spec
   and c.package_units = c.units
   and c.firm_id = d.firm_id
   and c.storage = e.storage_code   
   and a.drug_code = f.drug_code(+)
   and a.drug_code = g.drug_code(+)
   and a.drug_code = h.drug_code(+)
   and a.drug_code = i.drug_code(+)
   and e.serial_no is not null
   and b.std_indicator in (0, 1)
   and (c.supply_indicator = 1 or c.drug_bz = 1)
   and (d.stop_date >= sysdate or d.stop_date is null)
   and d.start_date <= sysdate
;
 

[骛曲玛黑]

可以使用 OUTER APPLY 或者 LEFT JOIN LATERAL 来避免多次执行相同的子查询

SELECT
    dp.amount_per_package AS quantityPerPackage,
    dp.units AS packageUnitCode,
    dp.units AS packageUnit,
    c.quantity - NVL(SUM(db.quantity), 0) AS storeNum
FROM
    comm.drug_dict a
JOIN comm.drug_name_dict b ON a.drug_code = b.drug_code
JOIN pharmacy.drug_stock c ON a.drug_code = c.drug_code AND a.drug_spec = c.drug_spec AND a.units = c.units
OUTER APPLY (
    SELECT amount_per_package, units
    FROM comm.drug_price_list
    WHERE drug_code = a.drug_code
        AND min_spec = a.drug_spec
        AND drug_spec <> a.drug_spec
        AND firm_id = c.firm_id
        AND min_units = a.units
        AND units <> a.units
        AND start_date <= sysdate
        AND (stop_date >= sysdate OR stop_date IS NULL)
        AND ROWNUM <= 1
) dp
LEFT JOIN drug_presc_master_temp da ON da.dispensary = c.storage
LEFT JOIN drug_presc_detail_temp db ON da.presc_date = db.presc_date
    AND da.presc_no = db.presc_no
    AND db.drug_code = c.drug_code
    AND db.drug_spec = c.drug_spec
    AND db.firm_id = c.firm_id
    AND db.package_units = c.units
    AND db.quantity > 0
LEFT JOIN pharmacy.drug_storage_dept e ON c.storage = e.storage_code
LEFT JOIN pharmacy.drug_kssxx g ON a.drug_code = g.drug_code
LEFT JOIN pharmacy.drug_jbyw h ON a.drug_code = h.drug_code
LEFT JOIN pharmacy.drug_same_yp i ON a.drug_code = i.drug_code
LEFT JOIN pharmacy.kss_drug_ps f ON a.drug_code = f.drug_code
WHERE
    e.serial_no IS NOT NULL
    AND b.std_indicator IN (0, 1)
    AND (c.supply_indicator = 1 OR c.drug_bz = 1)
    AND (dp.stop_date >= sysdate OR dp.stop_date IS NULL)
    AND dp.start_date <= sysdate;

[Dragon_In_Sky]

感谢大神，确实没见过OUTER APPLY，还得再学习