不当使用 union all 导致的SQL解析时间过长的问题优化

在帮助用户优化应用过程中，发现用户大量使用union all 导致SQL解析非常缓慢的问题。考虑到这个问题很有代表意义，我觉得很有必要对于问题进行总结。

一、用户例子

WITH company_user_temp AS (SELECT '00629999000100260000' AS company_code
 FROM dual
 UNION ALL
 SELECT '00250033000000000000' AS company_code FROM dual
 UNION ALL
 SELECT '00630005000300000000' AS company_code FROM dual
 UNION ALL
 SELECT '00460207000000000000' AS company_code FROM dual
 UNION ALL
 SELECT '00420089000000000000' AS company_code FROM dual
 UNION ALL
 SELECT '00630008000100000000' AS company_code FROM dual
 UNION ALL
 SELECT '00630013001000000000' AS company_code FROM dual
 UNION ALL
 SELECT '00620035001900000000' AS company_code FROM dual
。。。

　

用户大量使用值的union all，导致90%的时间耗在SQL解析上。

         ->  Seq Scan on dual dual_1458  (cost=0.00..1.01 rows=1 width=0) (actual time=0.003..0.004 rows=1 loops=1)
         ->  Seq Scan on dual dual_1459  (cost=0.00..1.01 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1)
         ->  Seq Scan on dual dual_1460  (cost=0.00..1.01 rows=1 width=0) (actual time=0.003..0.004 rows=1 loops=1)
         ->  Seq Scan on dual dual_1461  (cost=0.00..1.01 rows=1 width=0) (actual time=0.003..0.004 rows=1 loops=1)
 Planning Time: 5081.423 ms
 Execution Time: 43.726 ms
(1466 rows)

Time: 5230.600 ms (00:05.231)

 

二、问题分析

由于SQL有大量的union all，针对union all 的每个部分，SQL 都要进行解析。 由于整个SQL涉及2000多张表 （dual），整个性能非常差。考虑以下修改方式：

with company_user_temp as (select  * from (values('00629999000100260000'), ('00250033000000000000')) as company_code )
select count(*) from company_user_temp;

　　

通过这种方式，可以减少整条SQL涉及表的数量，提升SQL解析的性能。

三、验证分析

由于SQL过长，无法实际修改验证，构建以下动态SQL进行验证。

1、union 方式

test=# declare
test-#   v_sql text;
test-# begin
test-#   v_sql:= 'WITH company_user_temp AS (';
test-#   for i in 1..2000 loop
test-#     v_sql:=v_sql||'select '||i||' as company_code from dual union all ';
test-#   end loop;
test-#   v_sql:=substr(v_sql,0,length(v_sql) - 10) ||') select count(*) from company_user_temp';
test-#   execute immediate v_sql;
test-# end;
test-# /
ANONYMOUS BLOCK
Time: 6735.076 ms (00:06.735)
test=#

 

2、values 方式

test=# declare
test-#   v_sql text;
test-# begin
test-#   v_sql := 'with company_code as (select * from (values(';
test-#   for i in 1..2000 loop
test-#     v_sql:=v_sql||i||'),(';
test-#   end loop;
test-#   v_sql:=substr(v_sql,0,length(v_sql) - 2)||') as company_code ) select count(*) from company_code';
test-#   execute immediate v_sql;
test-# end;
test-# /
ANONYMOUS BLOCK
Time: 10.325 ms
test=#

　　

结论：可以看到，这样修改后，SQL 性能得到了大幅提升。