请问对于数据仓库里的查询语句,如果用到很多临时表with table as和group by会不会消耗大量的临时表空间,有没有更好的查询方法,比如是不是创建create一个本地表用于存放临时的数据,用完之后再把表drop掉好些呢?sql语句大致结构如下: INSERT INTO 目的表名 NOLOGGING WITH
tab_1 AS
(SELECT ... from tab_a WHERE ... GROUP BY ...),
tab_2 AS
(SELECT ... from tab_b WHERE ... GROUP BY ...),
tab_3 AS
(SELECT ... from tab_c WHERE ... GROUP BY ...),
tab_4 AS
(SELECT ... from tab_d WHERE ... GROUP BY ...),
tab_5 AS
(SELECT ... from tab_e WHERE ... GROUP BY ...)
select sum(tab_1.col),sum(tab_2.col),sum(tab_3.col),sum(tab_4.col),sum(tab_5.col) from
(select tab_1.col,...
union all
select tab_2.col,...
union all
select tab_3.col,...
union all
select tab_4.col,...
union all
select tab_5.col,...
)
tab_1 AS
(SELECT ... from tab_a WHERE ... GROUP BY ...),
tab_2 AS
(SELECT ... from tab_b WHERE ... GROUP BY ...),
tab_3 AS
(SELECT ... from tab_c WHERE ... GROUP BY ...),
tab_4 AS
(SELECT ... from tab_d WHERE ... GROUP BY ...),
tab_5 AS
(SELECT ... from tab_e WHERE ... GROUP BY ...)
select sum(tab_1.col),sum(tab_2.col),sum(tab_3.col),sum(tab_4.col),sum(tab_5.col) from
(select tab_1.col,...
union all
select tab_2.col,...
union all
select tab_3.col,...
union all
select tab_4.col,...
union all
select tab_5.col,...
)
and refresh the material view regularly
也不会用到临时表