下面这个生产系统上的SQL,开了8个并行, 执行时间2小时:
原SQL简化如下:
INSERT INTO dest
(
SELECT ACCOUNT_NUMBER, A.ORG_UNIT_ID , CUR_BOOK_BAL * A.BL AS CUR_BOOK_BAL
FROM T, A
WHERE SUBSTR (T.ACCOUNT_NUMBER, 1, LENGTH (A.ACCOUNT_ID)) = A.ACCOUNT_ID
UNION ALL
SELECT ACCOUNT_NUMBER, T.ORG_UNIT_ID , CUR_BOOK_BAL * (1 - A.BL) AS CUR_BOOK_BAL
FROM T, A
WHERE SUBSTR (T.ACCOUNT_NUMBER, 1, LENGTH (A.ACCOUNT_ID)) = (CASE WHEN 1 - A.BL <> 0 THEN A.ACCOUNT_ID END)
);
其中T表170万记录, A表5000条记录, 没有任何索引.
现在想把这个SQL优化到5秒以内,不要并行, union all也去掉, 能不能实现?
已经有人给出一个with的改写方法, 大家可以参考一下.效率提升能接近1倍, 但是没有去掉union all,离5秒以内的目标也还有很大差距:
INSERT INTO dest
WITH TEMP AS
(
SELECT ACCOUNT_NUMBER, A.ORG_UNIT_ID ID1, T.ORG_UNIT_ID ID2, CUR_BOOK_BAL, A.BL
FROM T, A
WHERE SUBSTR (T.ACCOUNT_NUMBER, 1, LENGTH (A.ACCOUNT_ID)) = A.ACCOUNT_ID
)
SELECT ACCOUNT_NUMBER , ID1 AS ORG_UNIT_ID,CUR_BOOK_BAL * BL AS CUR_BOOK_BAL
FROM TEMP
UNION ALL
SELECT ACCOUNT_NUMBER , ID2 AS ORG_UNIT_ID,CUR_BOOK_BAL * (1 - BL) AS CUR_BOOK_BAL
FROM TEMP
WHERE 1 - BL <> 0;
