reate archive table

[85]Thanks guys for the replies... Now, I want to d...... Tso P Jan 20,
[86]It depends on what you want to delete. Do you w...... Michel Cadot
[88]Thanks guys... I have a large table that we wan...... Tso P Jan 20,
[89]Hello Tso, Philip has given sound advice. If y...... Bruno Vroman
[90]Tso, about <i> WHERE MYDATE_COLUMN <= ADD_M...... Bruno Vroman

Subject: create archive table
Os info: RHEL5
Oracle info: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Message: Hi Guys
I want to create a table x from table y where table x will have data
older than 3 months.
What is the best solution that I can use for this task.
Please Help!!!
Thanks in advance...


Subject: Re: create archive table
Message: Hi Tso,
It is almost literally as you put it:
create table x as select * from y where
sdf<to_date('01-10-2015','DD-MM-YYYY');
Reagards, Philip


Subject: Re: create archive table
Message: Hello,
Another idea, much more initial work but the benefice comes every month
later...
- make your table a partitioned table, one partition per month
- make the archive table the same.
Then, once per month, you will simply have to swap one partition from
'current' to 'archived'. This can be done with the help of an empty
standard table with same structure:
- exchange old partition of 'current' with empty table; the old
partition is now a standard table and you can
- exchange it with an empty partition created on 'archived'
Best regards,
Bruno Vroman.


Subject: Re: create archive table
Message: Thanks guys for the replies...
Now, I want to delete records older than 3 months...
I want to use the below where clause:
WHERE MYDATE_COLUMN <= ADD_MONTHS(TRUNC(SYSDATE), -3);
Will this perform the task or should I rather use:
WHERE MYDATE_COLUMN < ADD_MONTHS(TRUNC(SYSDATE), -3);
Please help!!!
Thanks in advance...


Subject: Re: create archive table
Message:
It depends on what you want to delete.
Do you want to delete records from "ADD_MONTHS(TRUNC(SYSDATE), -3)" or
not?
Regards
Michel


Subject: Re: create archive table
Message: Hi Tso,
For small tables there would be no problem.
For large tables without partitioning the advice is to use CTAS and
rename the table into place, followed by circumstantial DLL.
If you cannot afford ANY downtime start deleting (with autocommit on)
one month at a time. Of course you need to know the distribution:
select to_char(f.MYDATE_COLUMN,'YYYYMM'), count(*)
from MYLARGETABLE f
group by to_char(f.change_date,'YYYYMM')
order by to_char(f.change_date,'YYYYMM') desc

Dropping MV logs, indexes and foreign keys (disabeling triggers) speeds
up, but I don't know if this is acceptable.
HTH, Philip


Subject: Re: create archive table
Message: Thanks guys...
I have a large table that we want to archive data older than 3 months.
I have created an archive table using CTAS.
I want to delete all the records that are 3 months old from the
original table.
Thanks in advance...


Subject: Re: create archive table
Message: Hello Tso,
Philip has given sound advice.
If you have to delete a large portion of the rows, it is better to work
in the opposite direction, by "keeping what has to be kept".
First you create a new table with the data to keep: CREATE TABLE
to_keep AS SELECT * FROM mytable WHERE mydate >= DATE '2015-10-01'
(pick the right date, depending of what you have archived)
Then either you
- drop the old table (first make sure that you know all its
constraints, indexes, triggers, privileges), rename "to_keep" to
original name, rebuild indexes, redefines constraints, triggers, grants
privileges
- or you truncate the original table and inserts back the content of
to_keep.
One remark: beware if there are triggers on the table: do you want them
to be fired for each insert or delete...? You must examine this
carefully.
Note that the second option (insert back) is easier but consumes more
time and resource, I would not recommend it but if you don't feel OK
with first one it is a "workaround".
Note that the first option (replace table) is the opportunity to go to
partitioned table as I had mentioned; then in the future archiving will
be made much faster as it will simply be a matter of "moving" one
partition from "current table" to "archived table" without needing any
INSERT or DELETE. Looks like the best choice if you can afford the
partitioning option (license cost). In this case you don't simply
create "to_keep" with a "CTAS" but you precreate the partitioned table
and then you insert data into it.
Best regards,
Bruno.


Subject: Re: create archive table
Message: Tso,
about
WHERE MYDATE_COLUMN <= ADD_MONTHS(TRUNC(SYSDATE), -3);
Will this perform the task or should I rather use:
WHERE MYDATE_COLUMN < ADD_MONTHS(TRUNC(SYSDATE), -3);
Note that the difference is only about "one second", especially if the
MYDATE_COLUMN contains dates with hour:min:sec
More important is maybe to use ADD_MONTHS( TRUNC( SYSDATE, 'MM' ), -3 )
Indeed I don't thnink that you want to archive or keep dates with the
"border" 20-OCT-2015 but probably with complete months (archive up to
30-SEP-2015 23:59:59, keep since 01-OCT-2015 00:00:00, or another
month, this is your choice)
As far as I'm concerned, I wouldn't delete until a day at 00:00:00
because I see this as weird, I prefer to delete until "just before the
day at 00:00:00" so: either delete ... mydate < a_given_day, or keep
... mydate >= a_given_day.
((my understanding is that you will archive one month of data each
month, but maybe your idea is to archive one day of data every day?))
Best regards,
Bruno