现有分区表table_p以一个日期字段分区,一个分区存放一个月数据。
table_p1,table_p2,table_p3,table_p4,table_p5 ...... table_p12建立包含3个月数据的视图:方法1:
create or replace view v_1_3 as
select * from table_p where f_date >=20010100 and f_date <=20100399;create or replace view v_4_6 as
select * from table_p where f_date >=20010100 and f_date <=20100399;
方法2:create or replace view v_1_3 as
select * from table_p1 union all
select * from table_p2 union all
select * from table_p3;
create or replace view v_4_6 as
select * from table_p4 union all
select * from table_p5 union all
select * from table_p6;
table_p1,table_p2,table_p3,table_p4,table_p5 ...... table_p12建立包含3个月数据的视图:方法1:
create or replace view v_1_3 as
select * from table_p where f_date >=20010100 and f_date <=20100399;create or replace view v_4_6 as
select * from table_p where f_date >=20010100 and f_date <=20100399;
方法2:create or replace view v_1_3 as
select * from table_p1 union all
select * from table_p2 union all
select * from table_p3;
create or replace view v_4_6 as
select * from table_p4 union all
select * from table_p5 union all
select * from table_p6;
按方法1建立视图后,应用程序中执行下面的查询(查询半年内某员工的数据)select * from view v_1_3 where emp_id=&3 and f_date >=&1 and f_date <=&2;
select * from view v_4_6 where emp_id=&3 and f_date >=&1 and f_date <=&2;&1:20010115
&2:20100515当两个视图中都有符合条件的数据的时候,程序报错了(索引不能使用)
但是如果只有前三个月的数据的话,程序没问题很抱歉不能把所有log贴出来,不知道说清楚了没有。
能够执行一次select操作就能完成的,不要分两次执行,当然,大数据量可以用中间表。