select orderkey,
externorderkey,
type,
COMPANY,
ORDERDATE,
ORDERSTATUS,
count(sku) countsku,
sum(WEIGHT) WEIGHT,
sum(CUBE) CUBE,
carnum,
carrierroutestatus
from (select pk.orderkey,
o.externorderkey,
(select description
from codelkup
where listname = 'ORDERTYPE'
and code = o.type) type,
(select COMPANY
from storer
where type = '2'
and storerkey = o.consigneekey) COMPANY,
TO_CHAR(O.ORDERDATE, 'YYYY-MM-DD') ORDERDATE,
(select DESCRIPTION
from ORDERSTATUSSETUP
where code = O.status) ORDERSTATUS,
od.SKU,
SUM(PK.PDUDF3) / 1000 WEIGHT,
SUM(PK.CUBEUOM) / 1000000 CUBE,
(select susr5 from orders where orderkey = pk.orderkey) carnum,
o.carrierroutestatus
from pickdetail pk, orders o, orderdetail od, sku sku, STORER S
where pk.orderkey = od.orderkey
and pk.orderlinenumber = od.orderlinenumber
and od.orderkey = o.orderkey
and sku.sku = od.sku
and sku.storerkey = od.storerkey
and O.CONSIGNEEKEY = S.STORERKEY
and S.TYPE = '2'
-- and od.orderkey='0000005238'
group by pk.orderkey,
o.externorderkey,
o.type,
o.consigneekey,
TO_CHAR(O.ORDERDATE, 'YYYY-MM-DD'),
O.status,
od.sku,
o.carrierroutestatus)
group by orderkey,
externorderkey,
type,
COMPANY,
ORDERDATE,
ORDERSTATUS,
carnum,
carrierroutestatus
order by orderkey desc
哪位大神可以幫小弟優化一下sql，查詢速度太慢了，搞不出來了
uj5u.com熱心網友回復：
1、減少表的嵌套次數2、適當的增加索引
3、以上根據執行計劃才好針對性的分析
uj5u.com熱心網友回復：
把子查詢換成中間表試試uj5u.com熱心網友回復：
1、幾個標量子查詢改為連接查詢，具體是type、COMPANY、ORDERSTATUS、carnum四個欄位（就是不放在select里面，直接與from的表連接）2、兩個分組group by 是重復無意義的，第二個分組完全可以去掉（在第一步完成的情況下）
uj5u.com熱心網友回復：
如果效率還是比較低，那就得看表結構和資料量了，這個是沒辦法給答復的，，，，得具體分析轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/102138.html
標籤：開發
上一篇：oracle 11g