我肯定考虑多的减少扫描次数.这是我上次优化的一个sql.原sql.执行时间为3800多秒.虽然这七八张表都是百万级.但是这个时间肯定是无法忍受的. SELECT INFORID AS CATEGORYID, INFORNAME AS CATEGORYNAME, FIRSTSUM, CURRECSUM, CURSENDSUM, RESIDUALSUM, QNSUM, QWSUM, NDSUM, CDSUM, JGSUM, MSUM, BFSUM, JNSUM, JWSUM, ZHSUM FROM BASICCODE A, (SELECT CATEGORYID, SUM(FIRSTSUM) AS FIRSTSUM, SUM(CURRECSUM) AS CURRECSUM, SUM(CURSENDSUM) AS CURSENDSUM, SUM(CURMOVESUM) AS NDSUM, SUM(RESIDUALSUM) AS RESIDUALSUM FROM M_STOCKACCOUNT A, M_MATERIALCODE B WHERE A.MATERIALID = B.MATERIALID GROUP BY CATEGORYID) C, (SELECT b.CATEGORYID, SUM((NVL(FRECEIVE, 0) + NVL(FRECEIVE, 0)) * PRICE) AS QNSUM FROM M_RECMATERIAL A, M_MATERIALCODE B, M_SUPPLY C WHERE A.MATERIALID = B.MATERIALID AND a.VERIF IS NOT NULL AND a.BILLKINDS = '0005' AND A.SUPPLYID = C.SUPPLYID AND C.PROVINCE = '65' GROUP BY b.CATEGORYID) D, (SELECT CATEGORYID, SUM((NVL(FRECEIVE, 0) + NVL(FRECEIVE, 0)) * PRICE) AS QWSUM FROM M_RECMATERIAL A, M_MATERIALCODE B, M_SUPPLY C WHERE A.MATERIALID = B.MATERIALID AND VERIF IS NOT NULL AND BILLKINDS = '0005' AND A.SUPPLYID = C.SUPPLYID AND C.PROVINCE <> '65' GROUP BY CATEGORYID) E, (SELECT CATEGORYID, SUM((NVL(FRECEIVE, 0) + NVL(FRECEIVE, 0)) * PRICE) AS CDSUM FROM M_RECMATERIAL A, M_MATERIALCODE B WHERE A.MATERIALID = B.MATERIALID AND VERIF IS NULL AND BILLKINDS = '0002' GROUP BY CATEGORYID) G, (SELECT CATEGORYID, SUM((NVL(FRECEIVE, 0) + NVL(FRECEIVE, 0)) * PRICE) AS JGSUM FROM M_RECMATERIAL A, M_MATERIALCODE B WHERE A.MATERIALID = B.MATERIALID AND VERIF IS NULL AND BILLKINDS = '0003' GROUP BY CATEGORYID) H, (SELECT CATEGORYID, SUM((PRICE - OLDPRICE) * AMOUNT) AS MSUM FROM M_CHANGEPRICE A, M_MATERIALCODE B WHERE A.MATERIALID = B.MATERIALID AND VERIF IS NULL GROUP BY CATEGORYID) I, (SELECT CATEGORYID, SUM((NVL(FSEND, 0) + NVL(FSENDS, 0)) * PRICE) AS JNSUM FROM M_SENDMATERIAL A, M_MATERIALCODE B, USEUNITCODE D WHERE A.MATERIALID = B.MATERIALID AND USEDIRECTID IS NULL AND A.USEUNITID = D.USEUNITID AND D.USEUNITKINDS = '0001' AND VERIF IS NULL GROUP BY CATEGORYID) J, (SELECT CATEGORYID, SUM((NVL(FSEND, 0) + NVL(FSENDS, 0)) * PRICE) AS JWSUM FROM M_SENDMATERIAL A, M_MATERIALCODE B, USEUNITCODE D WHERE A.MATERIALID = B.MATERIALID AND USEDIRECTID IS NULL AND A.USEUNITID = D.USEUNITID AND D.USEUNITKINDS = '0002' AND VERIF IS NULL GROUP BY CATEGORYID) K, (SELECT CATEGORYID, SUM((NVL(FSEND, 0) + NVL(FSENDS, 0)) * PRICE) AS ZHSUM FROM M_SENDMATERIAL A, M_MATERIALCODE B WHERE A.MATERIALID = B.MATERIALID AND USEDIRECTID IS NOT NULL AND VERIF IS NULL AND A.BILLKINDS = '0004' GROUP BY CATEGORYID) L, (SELECT CATEGORYID, SUM((NVL(FSEND, 0) + NVL(FSENDS, 0)) * PRICE) AS BFSUM FROM M_SENDMATERIAL A, M_MATERIALCODE B WHERE A.MATERIALID = B.MATERIALID AND VERIF IS NULL AND A.BILLKINDS = '0017' GROUP BY CATEGORYID) F WHERE A.GROUPID = '0009' AND A.INFORID = C.CATEGORYID(+) AND A.INFORID = D.CATEGORYID(+) AND A.INFORID = E.CATEGORYID(+) AND A.INFORID = F.CATEGORYID(+) AND A.INFORID = G.CATEGORYID(+) AND A.INFORID = H.CATEGORYID(+) AND A.INFORID = I.CATEGORYID(+) AND A.INFORID = J.CATEGORYID(+) AND A.INFORID = K.CATEGORYID(+) AND A.INFORID = L.CATEGORYID(+) ORDER BY INFORID经过分析.所有的group by 条件都是一致.且有一公共表.经提取出现. 然后把里面相当的group by 写成函数. 我例取一个函数如下: CREATE OR REPLACE FUNCTION F_M_SUM(AS_BILLKINDS VARCHAR2, AS_CATEGORYID VARCHAR2) RETURN NUMBER IS /* 物资领导查询 报废金额之和 作者:tony 时间:2005-01-25 */ V_RESULT NUMBER(15, 2);BEGIN SELECT nvl(SUM((NVL(FSEND, 0) + NVL(FSENDS, 0)) * PRICE),0) INTO V_RESULT FROM M_SENDMATERIAL A, M_MATERIALCODE B WHERE A.MATERIALID = B.MATERIALID AND A.BILLKINDS = AS_BILLKINDS AND B.CATEGORYID = AS_CATEGORYID; RETURN V_RESULT; EXCEPTION WHEN OTHERS THEN RETURN - 1; END F_M_SUM; 这样.语句就变得非常简单且高效.经测试.(在已有索引的情况下).执行时间为9秒.且服务器为pc机.700 多M内存. 变更的语句如下: SELECT INFORID AS CATEGORYID, INFORNAME AS CATEGORYNAME, F_M_SUM('0017', INFORID) BFSUM, F_M_SUM('0004', INFORID) ZHSUM, F_M_JSUM('0002', INFORID) JWSUM, F_M_JSUM('0001', INFORID) JNSUM, F_M_MSUM(INFORID) MSUM, F_M_JGSUM('0003', INFORID) JGSUM, F_M_JGSUM('0002', INFORID) CDSUM, F_M_QWSUM('0005', '65', INFORID, 2) QWSUM, F_M_QWSUM('0005', '65', INFORID, 1) QNSUM, nvl(SUM(FIRSTSUM),0) AS FIRSTSUM, nvl(SUM(CURRECSUM),0) AS CURRECSUM, nvl(SUM(CURSENDSUM),0) AS CURSENDSUM, nvl(SUM(CURMOVESUM),0) AS NDSUM, nvl(SUM(RESIDUALSUM),0) AS RESIDUALSUM FROM BASICCODE A, M_MATERIALCODE B, M_STOCKACCOUNT C WHERE A.GROUPID = '0009' AND A.INFORID = B.CATEGORYID AND B.MATERIALID = C.MATERIALID(+) GROUP BY INFORID, INFORNAME
SELECT INFORID AS CATEGORYID,
INFORNAME AS CATEGORYNAME,
FIRSTSUM,
CURRECSUM,
CURSENDSUM,
RESIDUALSUM,
QNSUM,
QWSUM,
NDSUM,
CDSUM,
JGSUM,
MSUM,
BFSUM,
JNSUM,
JWSUM,
ZHSUM
FROM BASICCODE A,
(SELECT CATEGORYID,
SUM(FIRSTSUM) AS FIRSTSUM,
SUM(CURRECSUM) AS CURRECSUM,
SUM(CURSENDSUM) AS CURSENDSUM,
SUM(CURMOVESUM) AS NDSUM,
SUM(RESIDUALSUM) AS RESIDUALSUM
FROM M_STOCKACCOUNT A, M_MATERIALCODE B
WHERE A.MATERIALID = B.MATERIALID
GROUP BY CATEGORYID) C,
(SELECT b.CATEGORYID,
SUM((NVL(FRECEIVE, 0) + NVL(FRECEIVE, 0)) * PRICE) AS QNSUM
FROM M_RECMATERIAL A, M_MATERIALCODE B, M_SUPPLY C
WHERE A.MATERIALID = B.MATERIALID
AND a.VERIF IS NOT NULL
AND a.BILLKINDS = '0005'
AND A.SUPPLYID = C.SUPPLYID
AND C.PROVINCE = '65'
GROUP BY b.CATEGORYID) D,
(SELECT CATEGORYID,
SUM((NVL(FRECEIVE, 0) + NVL(FRECEIVE, 0)) * PRICE) AS QWSUM
FROM M_RECMATERIAL A, M_MATERIALCODE B, M_SUPPLY C
WHERE A.MATERIALID = B.MATERIALID
AND VERIF IS NOT NULL
AND BILLKINDS = '0005'
AND A.SUPPLYID = C.SUPPLYID
AND C.PROVINCE <> '65'
GROUP BY CATEGORYID) E,
(SELECT CATEGORYID,
SUM((NVL(FRECEIVE, 0) + NVL(FRECEIVE, 0)) * PRICE) AS CDSUM
FROM M_RECMATERIAL A, M_MATERIALCODE B
WHERE A.MATERIALID = B.MATERIALID
AND VERIF IS NULL
AND BILLKINDS = '0002'
GROUP BY CATEGORYID) G,
(SELECT CATEGORYID,
SUM((NVL(FRECEIVE, 0) + NVL(FRECEIVE, 0)) * PRICE) AS JGSUM
FROM M_RECMATERIAL A, M_MATERIALCODE B
WHERE A.MATERIALID = B.MATERIALID
AND VERIF IS NULL
AND BILLKINDS = '0003'
GROUP BY CATEGORYID) H,
(SELECT CATEGORYID, SUM((PRICE - OLDPRICE) * AMOUNT) AS MSUM
FROM M_CHANGEPRICE A, M_MATERIALCODE B
WHERE A.MATERIALID = B.MATERIALID
AND VERIF IS NULL
GROUP BY CATEGORYID) I,
(SELECT CATEGORYID,
SUM((NVL(FSEND, 0) + NVL(FSENDS, 0)) * PRICE) AS JNSUM
FROM M_SENDMATERIAL A, M_MATERIALCODE B, USEUNITCODE D
WHERE A.MATERIALID = B.MATERIALID
AND USEDIRECTID IS NULL
AND A.USEUNITID = D.USEUNITID
AND D.USEUNITKINDS = '0001'
AND VERIF IS NULL
GROUP BY CATEGORYID) J,
(SELECT CATEGORYID,
SUM((NVL(FSEND, 0) + NVL(FSENDS, 0)) * PRICE) AS JWSUM
FROM M_SENDMATERIAL A, M_MATERIALCODE B, USEUNITCODE D
WHERE A.MATERIALID = B.MATERIALID
AND USEDIRECTID IS NULL
AND A.USEUNITID = D.USEUNITID
AND D.USEUNITKINDS = '0002'
AND VERIF IS NULL
GROUP BY CATEGORYID) K,
(SELECT CATEGORYID,
SUM((NVL(FSEND, 0) + NVL(FSENDS, 0)) * PRICE) AS ZHSUM
FROM M_SENDMATERIAL A, M_MATERIALCODE B
WHERE A.MATERIALID = B.MATERIALID
AND USEDIRECTID IS NOT NULL
AND VERIF IS NULL
AND A.BILLKINDS = '0004'
GROUP BY CATEGORYID) L,
(SELECT CATEGORYID,
SUM((NVL(FSEND, 0) + NVL(FSENDS, 0)) * PRICE) AS BFSUM
FROM M_SENDMATERIAL A, M_MATERIALCODE B
WHERE A.MATERIALID = B.MATERIALID
AND VERIF IS NULL
AND A.BILLKINDS = '0017'
GROUP BY CATEGORYID) F
WHERE A.GROUPID = '0009'
AND A.INFORID = C.CATEGORYID(+)
AND A.INFORID = D.CATEGORYID(+)
AND A.INFORID = E.CATEGORYID(+)
AND A.INFORID = F.CATEGORYID(+)
AND A.INFORID = G.CATEGORYID(+)
AND A.INFORID = H.CATEGORYID(+)
AND A.INFORID = I.CATEGORYID(+)
AND A.INFORID = J.CATEGORYID(+)
AND A.INFORID = K.CATEGORYID(+)
AND A.INFORID = L.CATEGORYID(+)
ORDER BY INFORID经过分析.所有的group by 条件都是一致.且有一公共表.经提取出现.
然后把里面相当的group by 写成函数.
我例取一个函数如下:
CREATE OR REPLACE FUNCTION F_M_SUM(AS_BILLKINDS VARCHAR2,
AS_CATEGORYID VARCHAR2) RETURN NUMBER IS
/*
物资领导查询
报废金额之和 作者:tony
时间:2005-01-25
*/
V_RESULT NUMBER(15, 2);BEGIN
SELECT nvl(SUM((NVL(FSEND, 0) + NVL(FSENDS, 0)) * PRICE),0)
INTO V_RESULT
FROM M_SENDMATERIAL A, M_MATERIALCODE B
WHERE A.MATERIALID = B.MATERIALID
AND A.BILLKINDS = AS_BILLKINDS
AND B.CATEGORYID = AS_CATEGORYID;
RETURN V_RESULT;
EXCEPTION
WHEN OTHERS THEN
RETURN - 1;
END F_M_SUM;
这样.语句就变得非常简单且高效.经测试.(在已有索引的情况下).执行时间为9秒.且服务器为pc机.700 多M内存.
变更的语句如下:
SELECT INFORID AS CATEGORYID,
INFORNAME AS CATEGORYNAME,
F_M_SUM('0017', INFORID) BFSUM,
F_M_SUM('0004', INFORID) ZHSUM,
F_M_JSUM('0002', INFORID) JWSUM,
F_M_JSUM('0001', INFORID) JNSUM,
F_M_MSUM(INFORID) MSUM,
F_M_JGSUM('0003', INFORID) JGSUM,
F_M_JGSUM('0002', INFORID) CDSUM,
F_M_QWSUM('0005', '65', INFORID, 2) QWSUM,
F_M_QWSUM('0005', '65', INFORID, 1) QNSUM,
nvl(SUM(FIRSTSUM),0) AS FIRSTSUM,
nvl(SUM(CURRECSUM),0) AS CURRECSUM,
nvl(SUM(CURSENDSUM),0) AS CURSENDSUM,
nvl(SUM(CURMOVESUM),0) AS NDSUM,
nvl(SUM(RESIDUALSUM),0) AS RESIDUALSUM
FROM BASICCODE A, M_MATERIALCODE B, M_STOCKACCOUNT C
WHERE A.GROUPID = '0009'
AND A.INFORID = B.CATEGORYID
AND B.MATERIALID = C.MATERIALID(+)
GROUP BY INFORID, INFORNAME
yxxx(小孬)太谦虚了!
to
rivey(星星点灯)你指的够好是多好?难道要用巨型机?