IBM Db2 Sql查询优化

Question

我有一个疑问：

select DBSAITE.MITEM, DBSAITE.MPDSC, DBSAITE.MUTMS, MVSACPE.COSTUN,  PCSACST.CSTTYAPP, 
SUM(PCSACST.CSTBURD1 + PCSACST.CSTBURD2 + PCSACST.CSTLABOR + 
PCSACST.CSTMAT + PCSACST.CSTSETUP + PCSACST.CSTEXTRN) as COSTO_STD 
from MVSACPE, PCSACST, DBSAITE
WHERE PCSACST.CSTCOSC = MVSACPE.CODSOC
AND PCSACST.CSTITEM = MVSACPE.CODITM
AND MVSACPE.CODITM = DBSAITE.MITEM
AND DBSAITE.MCOSC = 'GRS'
AND MVSACPE.CODSOC = 'GRS23'
AND MVSACPE.DTPERI = '201512'
AND MVSACPE.CODMAG = 'ALL'
AND MVSACPE.CODCOS = 'MPR' 
AND PCSACST.CSTDATE = '20150630'
AND PCSACST.CSTTYPE = 'SG'
GROUP BY DBSAITE.MITEM, DBSAITE.MPDSC, DBSAITE.MUTMS, MVSACPE.COSTUN,PCSACST.CSTTYAPP

我想以某种方式优化查询，因为join可以处理数十万条记录，而且速度非常慢(3分钟)。

有什么建议吗？

Answer 1

首先，使用显式JOIN语法重写查询。如果没有别的办法，这就更容易看出该做什么：

select DBSAITE.MITEM, DBSAITE.MPDSC, DBSAITE.MUTMS, MVSACPE.COSTUN,  PCSACST.CSTTYAPP, 
SUM(PCSACST.CSTBURD1 + PCSACST.CSTBURD2 + PCSACST.CSTLABOR + 
PCSACST.CSTMAT + PCSACST.CSTSETUP + PCSACST.CSTEXTRN) as COSTO_STD 
from MVSACPE JOIN
     PCSACST
     ON PCSACST.CSTCOSC = MVSACPE.CODSOC AND
        PCSACST.CSTITEM = MVSACPE.CODITM JOIN
     DBSAITE
     ON MVSACPE.CODITM = DBSAITE.MITEM
WHERE DBSAITE.MCOSC = 'GRS' AND
      MVSACPE.CODSOC = 'GRS23' AND
      MVSACPE.DTPERI = '201512' AND
      MVSACPE.CODMAG = 'ALL' AND
      MVSACPE.CODCOS = 'MPR' AND
      PCSACST.CSTDATE = '20150630' AND
      PCSACST.CSTTYPE = 'SG'
GROUP BY DBSAITE.MITEM, DBSAITE.MPDSC, DBSAITE.MUTMS, MVSACPE.COSTUN,PCSACST.CSTTYAPP;

索引可能有助于查询。目前尚不清楚哪些条件是最具选择性的。由于缺乏其他信息，最好从以下几个方面着手：

- `MVSACPE(CODSOC, DTPERI, CODMAG, CODCOS, CODITM)`
- `PCSACST(CSTCOSC, CSTITEM, CSTDATE, CSTTYPE)`
- `DBSAITE(MITEM, MCOSC)`