Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Large table monthly switch design problem
here is the scenario I was speaking about. No need for UNION ALL views:
create table fact_base(
year_num number(4) not null, somedata number)
partition by list(year_num)
(partition p2002 values (2002),
partition p2003 values(2003))
Table created
insert into fact_base values(2002, 1232) 1 row inserted
insert into fact_base values(2002, 1233) 1 row inserted
insert into fact_base values(2003, 123456) 1 row inserted
insert into fact_base values(2003, 456123) 1 row inserted
create view fact as
select * from fact_base where year_num between 2002 and 2003
View created
select * from fact
YEAR_NUM SOMEDATA
-------- --------
2002 1232 2002 1233
insert into fact_base values(2004, 6543) 1 row inserted
insert into fact_base values(2004, 4321) 1 row inserted
select * from fact_QA
YEAR_NUM SOMEDATA
-------- --------
2003 123456
2003 456123
2004 6543 2004 4321
drop view fact_QA
View dropped
alter table fact_base drop partition p2002 Table altered
create view fact as
select * from fact_base where year_num between 2003 and 2004
View created
select * from fact
YEAR_NUM SOMEDATA
-------- --------
2003 123456
2003 456123
2004 6543 2004 4321
drop view fact
View dropped
drop table fact_base
Table dropped
Received on Wed Jun 29 2005 - 10:54:40 CDT