Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Large table monthly switch design problem

Re: Large table monthly switch design problem

From: AK <AK_TIREDOFSPAM_at_hotmail.COM>
Date: 29 Jun 2005 08:54:40 -0700
Message-ID: <1120060480.386693.315860@g49g2000cwa.googlegroups.com>


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

    2003 123456
    2003 456123
4 rows selected

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

4 rows selected

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

4 rows selected    

drop view fact
View dropped  

drop table fact_base
Table dropped Received on Wed Jun 29 2005 - 10:54:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US