|
创建10个大型表的关联视图,用union 产生的速度变慢,请教应如何优化!每张表大约有200万条,每张表代表一天的数据,计保存10天,每天0:00删除第10天前的数据,用10张表删除时用truncate table 速度快,若用delete 语句则系统会出问题,由于用union 语句建立视图,用select 速度很慢,请教大家应如何优化视图。谢谢!
每张表用BEGINTIME,UNITID建立了唯一性索引!10张表每条记录都不会重复!
create or replace view unittrend as
( select UNITID,TRENDINDEX,BEGINTIME,TRENDVALUE,LABEL from unittrend_0
union
select UNITID,TRENDINDEX,BEGINTIME,TRENDVALUE,LABEL from unittrend_1
union
select UNITID,TRENDINDEX,BEGINTIME,TRENDVALUE,LABEL from unittrend_2
union
select UNITID,TRENDINDEX,BEGINTIME,TRENDVALUE,LABEL from unittrend_3
union
select UNITID,TRENDINDEX,BEGINTIME,TRENDVALUE,LABEL from unittrend_4
union
select UNITID,TRENDINDEX,BEGINTIME,TRENDVALUE,LABEL from unittrend_5
union
select UNITID,TRENDINDEX,BEGINTIME,TRENDVALUE,LABEL from unittrend_6
union
select UNITID,TRENDINDEX,BEGINTIME,TRENDVALUE,LABEL from unittrend_7
union
select UNITID,TRENDINDEX,BEGINTIME,TRENDVALUE,LABEL from unittrend_8
union
select UNITID,TRENDINDEX,BEGINTIME,TRENDVALUE,LABEL from unittrend_9
) |
|