问一个sql的优化

显示全部楼层 · 2014-5-9 08:24:37

在我们的生产系统，有一条sql语句，问一下大家该如何优化:
SELECT drug_export_detail.retail_price, drug_export_detail.purchase_price
FROM drug_export_detail, drug_export_master
WHERE (drug_export_detail.document_no = drug_export_master.document_no)
AND ((drug_export_master.STORAGE = :xx1)
AND (drug_export_detail.drug_code = :xx2)
AND (drug_export_detail.package_spec = :xx3)
AND (drug_export_detail.firm_id = :xx4)
)
AND export_date =

(SELECT MAX (export_date)

FROM drug_export_detail, drug_export_master

WHERE (drug_export_detail.document_no =

drug_export_master.document_no

)

AND ((drug_export_master.STORAGE = :xx1)

AND (drug_export_detail.drug_code = :xx2)

AND (drug_export_detail.package_spec = :xx3)

AND (drug_export_detail.firm_id = :xx4)

)
GROUP BY drug_export_master.STORAGE,

drug_export_detail.drug_code,

drug_export_detail.package_spec,

drug_export_detail.firm_id)
AND ROWNUM = 1;

千问 · 2014-5-9 08:24:37

憋不住问一句
是不是发错版面了?

千问 · 2014-5-9 08:24:37

没有，军惠的东西，熟悉的人许多，如果发到别的组，别人并不知道如何优化。
以及模式。

千问 · 2014-5-9 08:24:37

我觉得还是到开发板的人多些,这和行业没关系吧,
只认SQL不认行业

千问 · 2014-5-9 08:24:37

丫!
看来许多军惠的用户都不注意sql的优化问题

千问 · 2014-5-9 08:24:37

是许多的军惠用户还没看到帖子呢。
4、5年没接触军惠的东西了，都忘的差不多了。
是要取某药最近一次出库时的零售和采购价格？

千问 · 2014-5-9 08:24:37

正确/
这个语句是做采购计划时候执行的.

千问 · 2014-5-9 08:24:37

SELECT DISTINCT "OPERATION_BILL_ITEMS"."PATIENT_ID",

"OPERATION_BILL_ITEMS"."OPER_ID", "PAT_MASTER_INDEX"."NAME",

"PAT_MASTER_INDEX"."SEX", "PAT_MASTER_INDEX"."CHARGE_TYPE"
FROM "PAT_MASTER_INDEX",

"OPERATION_BILL_ITEMS",

"SURGERY"."OPERATION_MASTER"

WHERE ("SURGERY"."OPERATION_MASTER"."PATIENT_ID" =

"OPERATION_BILL_ITEMS"."PATIENT_ID"

)

AND ("SURGERY"."OPERATION_MASTER"."VISIT_ID" =

"OPERATION_BILL_ITEMS"."VISIT_ID"

)

AND ("SURGERY"."OPERATION_MASTER"."OPER_ID" =

"OPERATION_BILL_ITEMS"."OPER_ID"

)

AND ("OPERATION_BILL_ITEMS"."PATIENT_ID" =

"PAT_MASTER_INDEX"."PATIENT_ID"

)

AND ((TO_CHAR ("OPERATION_MASTER"."START_DATE_TIME",

'YYYY-MM-DD'

) = ：operating_date

)

AND ("OPERATION_BILL_ITEMS"."PERFORMED_BY" = ：performed_by)

)
再贴一个,现在看军惠的sql写的真烂!!!!
[ 本帖最后由 lfree 于 2008-6-6 10:10 编辑 ]

千问 · 2014-5-9 08:24:37

第一个SQL直接order by export_date后外面套一个ROWNUM = 1不行么？

千问 · 2014-5-9 08:24:37

原帖由 lfree 于 2008-6-4 11:50 发表
在我们的生产系统，有一条sql语句，问一下大家该如何优化:
SELECT drug_export_detail.retail_price, drug_export_detail.purchase_price
FROM drug_export_detail, drug_export_master
WHERE (drug_export_detail.document_no = drug_export_master.document_no)
AND ((drug_export_master.STORAGE = :xx1)
AND (drug_export_detail.drug_code = :xx2)
AND (drug_export_detail.package_spec = :xx3)
AND (drug_export_detail.firm_id = :xx4)
)
AND export_date =

(SELECT MAX (export_date)

FROM drug_export_detail, drug_export_master

WHERE (drug_export_detail.document_no =

drug_export_master.document_no

)

AND ((drug_export_master.STORAGE = :xx1)

AND (drug_export_detail.drug_code = :xx2)

AND (drug_export_detail.package_spec = :xx3)

AND (drug_export_detail.firm_id = :xx4)

)
GROUP BY drug_export_master.STORAGE,

drug_export_detail.drug_code,

drug_export_detail.package_spec,

drug_export_detail.firm_id)
AND ROWNUM = 1;

优化成这个行不？
select a.retail_price,a.purchase_price
from drug_export_detail a,

drug_export_master b,

(select max(export_date) max_export_date

from drug_export_detail m,

drug_export_master n

where m.document_no = n.docment_no

and n.storage = :xx1

and n.drug_code = :xx2

and n.package_spec = :xx3

and n.firm_id = :xx4) c
where a.docment_no = b.docment_no
and b.storage = :xx1
and b.drug_code = :xx2
and b.pacakge_spec = :xx3
and b.firm_id = :xx4
and a.export_date = c.max_export_date;

主表和明细表分别取两次是不可避免的，但是可以去掉不必要的group by ，这个对性能影响是比较大的