您好,有个疑问,我看到 | 11 | INDEX FAST FULL SCAN | IDX_IF_KC01_FROMNUMBER | 44M| 901M| | 54599 (1)| 00:10:56 | 占用了比较多的资源,那么调优的时候是要从这个地方入手吗?
-- 猜测ORDERS是主表,PICKDETAIL是明细 -- 变换成分别过滤后连接,应该比每个明细都用主表进行EXISTS判断要好 SELECT F.storerkey, F.sku, F.lot, F.loc, F.id, SUM (F.qty) AS qty FROM (SELECT D.orderkey, D.Storerkey, D.sku, D.lot, D.Loc, D.ID, D.qty, D.adddate FROM PICKDETAIL D WHERE -- 最好变换成 Adddate > .. AND Adddate < .. 的形式,可以利用 Adddate 上的索引 (D.Adddate + INTERVAL '8' HOUR) > TO_DATE (TO_CHAR (SYSDATE - 7, 'YYYY-MM-DD') || ' 07:55:00', 'YYYY-MM-DD HH24:MI:SS') AND (D.Adddate + INTERVAL '8' HOUR) < SYSDATE - INTERVAL '3' MINUTE ) F JOIN ( SELECT ORDERKEY, STORERKEY FROM ORDERS WHERE OHTYPE = 3 AND Storerkey = 'VWPL' -- NOT EXISTS 在主表这里判断,并且两个表的判断分开 AND NOT EXISTS (SELECT 1 FROM wh1.IF_kc01 WHERE fromnumber = ORDERS.EXTERNORDERKEY) AND NOT EXISTS (SELECT 1 FROM wh1.IF_kc02 WHERE barcode = ORDERS.EXTERNORDERKEY) ) E ON E.Storerkey = F.Storerkey AND E.Orderkey = F.ORDERKEY GROUP BY F.storerkey, F.sku, F.lot, F.loc, F.id
| 11 | INDEX FAST FULL SCAN | IDX_IF_KC01_FROMNUMBER | 44M| 901M| | 54599 (1)| 00:10:56 |
占用了比较多的资源,那么调优的时候是要从这个地方入手吗?
-- 变换成分别过滤后连接,应该比每个明细都用主表进行EXISTS判断要好
SELECT F.storerkey,
F.sku,
F.lot,
F.loc,
F.id,
SUM (F.qty) AS qty
FROM (SELECT D.orderkey,
D.Storerkey,
D.sku,
D.lot,
D.Loc,
D.ID,
D.qty,
D.adddate
FROM PICKDETAIL D
WHERE -- 最好变换成 Adddate > .. AND Adddate < .. 的形式,可以利用 Adddate 上的索引
(D.Adddate + INTERVAL '8' HOUR) > TO_DATE (TO_CHAR (SYSDATE - 7, 'YYYY-MM-DD')
|| ' 07:55:00',
'YYYY-MM-DD HH24:MI:SS')
AND (D.Adddate + INTERVAL '8' HOUR) < SYSDATE - INTERVAL '3' MINUTE
) F
JOIN (
SELECT ORDERKEY,
STORERKEY
FROM ORDERS
WHERE OHTYPE = 3
AND Storerkey = 'VWPL'
-- NOT EXISTS 在主表这里判断,并且两个表的判断分开
AND NOT EXISTS (SELECT 1
FROM wh1.IF_kc01
WHERE fromnumber = ORDERS.EXTERNORDERKEY)
AND NOT EXISTS (SELECT 1
FROM wh1.IF_kc02
WHERE barcode = ORDERS.EXTERNORDERKEY)
) E
ON E.Storerkey = F.Storerkey
AND E.Orderkey = F.ORDERKEY
GROUP BY F.storerkey,
F.sku,
F.lot,
F.loc,
F.id
不好意思啊,我还得需要时间消化一下,不是干开发的,对SQL不熟悉,我慢慢看一下,先谢谢楼上二位。看来对于SQL优化我还有很多路要走。不是一个两个个例能快速提升自己的