Select based on dates

[TechMaria]

Hi,

I need to create a table by selecting data from another table based on one of its columns (which is a date field named t_data_date)...

1) Create a new archive table (t_arc) which stores 6 months of latest data from table (t_data) ... t_data currently has over 80 million rows and has about 18 months of data ...

2) Recreate t_data so that it stores only 3 months of latest data ...

At the end of this, I should have two tables (t_data with 3 months of latest data and t_arc with 6 months of latest data)

I need a generic solution to this, coz this needs to be done once every 3 months ...

This is solution we right now have is :

1) create table t_arc as
select /*+ parallel 16 */ * from t_data
where substr(t_data_date,4,3) in
(select substr(sysdate,4,3) from dual
union
select substr(add_months(sysdate,-1),4,3) from dual
union
select substr(add_months(sysdate,-2),4,3) from dual
union
select substr(add_months(sysdate,-3),4,3) from dual
union
select substr(add_months(sysdate,-4),4,3) from dual
union
select substr(add_months(sysdate,-5),4,3) from dual)
/

<This creates t_arc with 6 months of latest data>

2) rename t_data to t_data_old;

3) create table t_data as
select /*+ parallel 16 */ * from t_arc
where substr(t_data_date,4,3) in
(select substr(sysdate,4,3) from dual
union
select substr(add_months(sysdate,-1),4,3) from dual
union
select substr(add_months(sysdate,-2),4,3) from dual)
/

4) Fix the index(s) ...

5) Drop table t_data_old;

<This re-creates t_data >

Is this a good approach ? I am sure someone will have a suggestion to speed up the entire process ...

Thanx

-Maria

 

[SantaMufasa]

Maria,

I don't know what all the UNION-ing buys you, but here is a simpler SELECT that should do what you want:

create table t_arc as
select /*+ parallel 16 */ * from t_data
where t_data_date >= trunc(add_months(sysdate,-6),'MM');

For today's date ('14-JUN-04'), the above SELECT yields all rows from midnight on the morning of '01-DEC-03' (six months ago).

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:36 (15Jun04) UTC (aka "GMT" and "Zulu"), 17:36 (14Jun04) Mountain Time)

 

[stefanhei]

Hi Maria,
have a look at partitioned tables.
You could have a partition "data" and another "arc".
Then you could split "arc"-partition to "arc" and "arc_old" (=older than 6 months), drop "arc_old", merge "data" and "arc" and split again (maybe with REBUILD UNUSABLE LOCAL INDEXES clause).

Stefan

 

[TechMaria]

Thanx a lot for the responses ...

Dave's solution does make my query look very decent ... I sure will use that ...

Stefan, if I use partitioned tables, then both arc and data partitions will be in 1 table, right ? and so unless we change our application code it will scan the entire table during an FTS ... arc is not expected to be queried much ...

Lemme know

Thanx again!

-Maria

 

[stefanhei]

Arc and data will be in the same table, but your applicationcode doesn't have to be changed. Name the partitioned table according to its content (maybe t_all) and create views t_data and t_arc that select from the partition you need. That way FTS on t_data will not scan the entire table too.

Stefan