关于临时表 和 临时表空间的使用问题！！！！！！！！在线等待！！！！！！！！！！

[smile_wu]

请问对于数据仓库里的查询语句，如果用到很多临时表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,...
)

[xiaoxiao1984]

create material view to solve it
and refresh the material view regularly

[smile_wu]

我这些tab_1到tab_5里面的查询都是带动态的查询条件的，物料视图能带输入参数吗？

[xiaoxiao1984]

you can create aggregated material view, then you can query the material view with dynamic conditions, just like you query ordinary tables and views.

[Andy__Huang]

不必建立那么多表吧?你的目标表可以加一个type类型字段,然后按不同类型分组求和;
也不会用到临时表

[watson110]

如果这5个表只是查询条件不同的话，就没有必要写那么多表了，一个循环就解决了，然后将循环后的结果插到临时表里，之后返回临时表就ok了

[wangsong145]

创建物化视图 CREATE MATERIALIZED VIEW_NAME AS SELECT 。。。