请你来试试这个SQL的优化,感受一下改写的效果

下面这个生产系统上的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;