ORACLE UNION ALL 与UNION 性能问题

Question

ORACLE UNION ALL 与UNION 性能问题现象：
一个存储过程用到了UNION ALL 或者 UNION 造成系统界面卡死
系统使用一周之后，就卡死了，把UNION ALL 改成 UNION 一下子就执行过去。再过一周又卡死了，又把UNION 改成UNION ALL又一下子执行过去了。此问题频繁出现现在，
暂时处理方案：修改存储过程，UNION ALL与UNION 两者之前进行切换。造成此问题是什么原因呀。有什么解决方案呀。。

Answer 1

额，太模糊。
怀疑你的sql逻辑写的有问题

Answer 2

SELECT DATE_TIME_FROM, DATE_TIME_TO
  FROM (SELECT MIN(ETD_TIME) AS DATE_TIME_FROM,
               MAX(ETD_TIME) AS DATE_TIME_TO,
               'ETD_TIME' AS TIME_TYPE
          FROM (SELECT CSC.ETD_TIME AS ETD_TIME, 'SE' AS SYSTEM_NO
                  FROM RP_ALLOCATION_EVENT RAE
                  JOIN TMP_QUERY TMP
                    ON TMP.JOB_ORDER_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_ALLOCATION_DETAIL RAD
                    ON RAD.ALLOCATION_EVENT_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_LEDGER_ITEM RLI
                    ON RLI.LEDGER_ITEM_ID = RAD.LEDGER_ITEM_ID
                  JOIN SZ_FREIGHT SF
                    ON SF.FREIGHT_ID = RLI.SOURCE_ID
                  JOIN CA_SE_MANIFEST CSM
                    ON CSM.BL_NO_ID = SF.JOB_ORDER_ID
                  JOIN CA_SH_CONSIGNMENT CSC
                    ON CSC.CONSIGN_ID = CSM.VOYAGE_ID
                 WHERE SF.SYSTEM_NO = 'SE'
                UNION ALL
                SELECT CSC.ETD_TIME AS ETD_TIME, 'SI' AS SYSTEM_NO
                  FROM RP_ALLOCATION_EVENT RAE
                  JOIN TMP_QUERY TMP
                    ON TMP.JOB_ORDER_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_ALLOCATION_DETAIL RAD
                    ON RAD.ALLOCATION_EVENT_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_LEDGER_ITEM RLI
                    ON RLI.LEDGER_ITEM_ID = RAD.LEDGER_ITEM_ID
                  JOIN SZ_FREIGHT SF
                    ON SF.FREIGHT_ID = RLI.SOURCE_ID
                  JOIN CA_SI_MANIFEST CSM
                    ON CSM.BL_ID = SF.JOB_ORDER_ID
                  JOIN CA_SH_CONSIGNMENT CSC
                    ON CSC.CONSIGN_ID = SF.JOB_ORDER_ID
                 WHERE SF.SYSTEM_NO = 'SI'
                UNION ALL
                SELECT CSC.ETD_TIME AS ETD_TIME, 'HU' AS SYSTEM_NO
                  FROM RP_ALLOCATION_EVENT RAE
                  JOIN TMP_QUERY TMP
                    ON TMP.JOB_ORDER_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_ALLOCATION_DETAIL RAD
                    ON RAD.ALLOCATION_EVENT_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_LEDGER_ITEM RLI
                    ON RLI.LEDGER_ITEM_ID = RAD.LEDGER_ITEM_ID
                  JOIN SZ_FREIGHT SF
                    ON SF.FREIGHT_ID = RLI.SOURCE_ID
                  JOIN CA_SH_CONSIGNMENT CSC
                    ON CSC.CONSIGN_ID = SF.JOB_ORDER_ID
                 WHERE SF.SYSTEM_NO = 'HU'
                UNION ALL
                SELECT CBH.IE_DATE AS ETD_TIME, 'BK' AS SYSTEM_NO
                  FROM RP_ALLOCATION_EVENT RAE
                  JOIN TMP_QUERY TMP
                    ON TMP.JOB_ORDER_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_ALLOCATION_DETAIL RAD
                    ON RAD.ALLOCATION_EVENT_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_LEDGER_ITEM RLI
                    ON RLI.LEDGER_ITEM_ID = RAD.LEDGER_ITEM_ID
                  JOIN SZ_FREIGHT SF
                    ON SF.FREIGHT_ID = RLI.SOURCE_ID
                  JOIN CA_BK_HEADER CBH
                    ON CBH.BK_HEADER_ID = SF.JOB_ORDER_ID
                 WHERE SF.SYSTEM_NO = 'BK'
                UNION ALL
                SELECT CBR.ETD_TIME AS ETD_TIME, 'BKR' AS SYSTEM_NO
                  FROM RP_ALLOCATION_EVENT RAE
                  JOIN TMP_QUERY TMP
                    ON TMP.JOB_ORDER_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_ALLOCATION_DETAIL RAD
                    ON RAD.ALLOCATION_EVENT_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_LEDGER_ITEM RLI
                    ON RLI.LEDGER_ITEM_ID = RAD.LEDGER_ITEM_ID
                  JOIN SZ_FREIGHT SF
                    ON SF.FREIGHT_ID = RLI.SOURCE_ID
                  JOIN CA_BK_REASSIGN CBR
                    ON CBR.BK_REASSIGN_ID = SF.JOB_ORDER_ID
                 WHERE SF.SYSTEM_NO = 'BK')) A;

Answer 3

就是下面一个语句出的问题，慢了我就把UNION ALL 改成UNION 就快了。
SELECT DATE_TIME_FROM, DATE_TIME_TO
  FROM (SELECT MIN(ETD_TIME) AS DATE_TIME_FROM,
               MAX(ETD_TIME) AS DATE_TIME_TO,
               'ETD_TIME' AS TIME_TYPE
          FROM (SELECT CSC.ETD_TIME AS ETD_TIME, 'SE' AS SYSTEM_NO
                  FROM RP_ALLOCATION_EVENT RAE
                  JOIN TMP_QUERY TMP
                    ON TMP.JOB_ORDER_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_ALLOCATION_DETAIL RAD
                    ON RAD.ALLOCATION_EVENT_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_LEDGER_ITEM RLI
                    ON RLI.LEDGER_ITEM_ID = RAD.LEDGER_ITEM_ID
                  JOIN SZ_FREIGHT SF
                    ON SF.FREIGHT_ID = RLI.SOURCE_ID
                  JOIN CA_SE_MANIFEST CSM
                    ON CSM.BL_NO_ID = SF.JOB_ORDER_ID
                  JOIN CA_SH_CONSIGNMENT CSC
                    ON CSC.CONSIGN_ID = CSM.VOYAGE_ID
                 WHERE SF.SYSTEM_NO = 'SE'
                UNION ALL
                SELECT CSC.ETD_TIME AS ETD_TIME, 'SI' AS SYSTEM_NO
                  FROM RP_ALLOCATION_EVENT RAE
                  JOIN TMP_QUERY TMP
                    ON TMP.JOB_ORDER_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_ALLOCATION_DETAIL RAD
                    ON RAD.ALLOCATION_EVENT_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_LEDGER_ITEM RLI
                    ON RLI.LEDGER_ITEM_ID = RAD.LEDGER_ITEM_ID
                  JOIN SZ_FREIGHT SF
                    ON SF.FREIGHT_ID = RLI.SOURCE_ID
                  JOIN CA_SI_MANIFEST CSM
                    ON CSM.BL_ID = SF.JOB_ORDER_ID
                  JOIN CA_SH_CONSIGNMENT CSC
                    ON CSC.CONSIGN_ID = SF.JOB_ORDER_ID
                 WHERE SF.SYSTEM_NO = 'SI'
                UNION ALL
                SELECT CSC.ETD_TIME AS ETD_TIME, 'HU' AS SYSTEM_NO
                  FROM RP_ALLOCATION_EVENT RAE
                  JOIN TMP_QUERY TMP
                    ON TMP.JOB_ORDER_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_ALLOCATION_DETAIL RAD
                    ON RAD.ALLOCATION_EVENT_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_LEDGER_ITEM RLI
                    ON RLI.LEDGER_ITEM_ID = RAD.LEDGER_ITEM_ID
                  JOIN SZ_FREIGHT SF
                    ON SF.FREIGHT_ID = RLI.SOURCE_ID
                  JOIN CA_SH_CONSIGNMENT CSC
                    ON CSC.CONSIGN_ID = SF.JOB_ORDER_ID
                 WHERE SF.SYSTEM_NO = 'HU'
                UNION ALL
                SELECT CBH.IE_DATE AS ETD_TIME, 'BK' AS SYSTEM_NO
                  FROM RP_ALLOCATION_EVENT RAE
                  JOIN TMP_QUERY TMP
                    ON TMP.JOB_ORDER_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_ALLOCATION_DETAIL RAD
                    ON RAD.ALLOCATION_EVENT_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_LEDGER_ITEM RLI
                    ON RLI.LEDGER_ITEM_ID = RAD.LEDGER_ITEM_ID
                  JOIN SZ_FREIGHT SF
                    ON SF.FREIGHT_ID = RLI.SOURCE_ID
                  JOIN CA_BK_HEADER CBH
                    ON CBH.BK_HEADER_ID = SF.JOB_ORDER_ID
                 WHERE SF.SYSTEM_NO = 'BK'
                UNION ALL
                SELECT CBR.ETD_TIME AS ETD_TIME, 'BKR' AS SYSTEM_NO
                  FROM RP_ALLOCATION_EVENT RAE
                  JOIN TMP_QUERY TMP
                    ON TMP.JOB_ORDER_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_ALLOCATION_DETAIL RAD
                    ON RAD.ALLOCATION_EVENT_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_LEDGER_ITEM RLI
                    ON RLI.LEDGER_ITEM_ID = RAD.LEDGER_ITEM_ID
                  JOIN SZ_FREIGHT SF
                    ON SF.FREIGHT_ID = RLI.SOURCE_ID
                  JOIN CA_BK_REASSIGN CBR
                    ON CBR.BK_REASSIGN_ID = SF.JOB_ORDER_ID
                 WHERE SF.SYSTEM_NO = 'BK')) A;

Answer 4

union会去重，去重就要排序，一般来说union要比union all慢一些。

Answer 5

都没解决问题呀
现在用 了UNION ALL反而更慢，改成UNION 反而更快。
慢的时候我修改存储过程的UNION ALL 成UNION 就快

Answer 6

搞这么多union all干什么，而且你这几个union all中，关联表前4个都是一样的，自己想想逻辑，合并成一次连接，这个sql效率太低，重新写一个吧.

Answer 7

如下写法性能怎么样 WITH A AS
               (SELECT SF.JOB_ORDER_ID
                  FROM RP_ALLOCATION_EVENT RAE
                  JOIN RP_ALLOCATION_DETAIL RAD
                    ON RAD.ALLOCATION_EVENT_ID = RAE.ALLOCATION_EVENT_ID
                  JOIN RP_LEDGER_ITEM RLI
                    ON RLI.LEDGER_ITEM_ID = RAD.LEDGER_ITEM_ID
                  JOIN SZ_FREIGHT SF
                    ON SF.FREIGHT_ID = RLI.SOURCE_ID
                  JOIN TMP_QUERY TMP
                    ON TMP.JOB_ORDER_ID = RAE.ALLOCATION_EVENT_ID)
              SELECT MIN(ETD_TIME) AS DATE_TIME_FROM,
                     MAX(ETD_TIME) AS DATE_TIME_TO
                FROM ((SELECT CSC.ETD_TIME
                         FROM A
                         JOIN CA_SI_MANIFEST CSM
                           ON CSM.BL_ID = A.JOB_ORDER_ID
                         JOIN CA_SH_CONSIGNMENT CSC
                           ON CSC.CONSIGN_ID = CSM.VOYAGE_ID) UNION ALL
                      (SELECT CSC.ETD_TIME
                         FROM A
                         JOIN CA_SI_MANIFEST CSM
                           ON CSM.BL_ID = A.JOB_ORDER_ID
                         JOIN CA_SH_CONSIGNMENT CSC
                           ON CSC.CONSIGN_ID = CSM.VOYAGE_ID) UNION ALL
                      (SELECT CSC.ETD_TIME
                         FROM A
                         JOIN CA_SH_CONSIGNMENT CSC
                           ON CSC.CONSIGN_ID = A.JOB_ORDER_ID) UNION ALL
                      (SELECT CBH.IE_DATE AS ETD_TIME
                         FROM A
                         JOIN CA_BK_HEADER CBH
                           ON CBH.BK_HEADER_ID = A.JOB_ORDER_ID) UNION ALL
                      (SELECT CBR.ETD_TIME AS ETD_TIME
                         FROM A
                         JOIN CA_BK_REASSIGN CBR
                           ON CBR.BK_REASSIGN_ID = A.JOB_ORDER_ID) UNION ALL
                      (SELECT CSC.ETD_TIME
                         FROM A
                         JOIN CA_SE_MANIFEST CSM
                           ON CSM.BL_NO_ID = A.JOB_ORDER_ID
                         JOIN CA_SH_CONSIGNMENT CSC
                           ON CSC.CONSIGN_ID = CSM.VOYAGE_ID));
          END;

Answer 8

这种sql语句你写出来就不觉得难受吗？
就不觉得维护都很难维护吗？
瞎搞！

Answer 9

为什么会造成UNION ALL 改成UNION 后性能突然加快。系统运行一个时间后又突然变慢，改回原来的UNION ALL又变快了。
造成上面问题的原因主要有哪些。

Answer 10

表设计是不可能动的了。表的索引都是有建的。
为什么会造成UNION ALL 改成UNION 后性能突然加快。系统运行一个时间后又突然会卡死，改回原来的UNION ALL又变快了。
造成上面问题的原因主要有哪些。

Answer 11

这SQL语句，我看出来了，你只是为了实现需求去写的，而没有考虑到性能问题

Answer 12

union all 不会去重，union会去重，业务逻辑就不一样，建议谨慎修改。
看你说的情况，应该和这个关系不大。
看样子像是DB间歇性的井喷，在哪本书上看到过这样例子，忘记了，明天找找看~

Answer 13

用的应该ORACLE数据库，是CBO查询器问题，第一次执行最快，然后就是一次执行比一次执行慢,可以加/*rule*/来解决,union all 肯定比union要快，优先考虑用union all。