Re: Archiving Large Tables

"Mill" <mill.h_at_ntlworld.com> wrote in message news:<hSAEc.540$TK1.188_at_newsfe3-win.server.ntli.net>...
> Hi
>
> I have a few tables in my db which are 2gb in size, a lot of this data is
> old and could do with moving to
> a new table to be archived so it would reduce the amount a procedure has to
> go through to
> return a number of records. Was is the best way to do this ?
>
> Perhaps have 2 tables
>
> customer_orders customers_orders_history
>
> copy the data from one table to another then remove the data from the
> orginal table (customer_orders)

A lot depends on information you haven't provided. Are you using LMT's? What version of Oracle are you on? Do these tables have constraints? Indices? How often will you be purging?

If you simply delete the records you may be setting yourself up for performance issues under some configurations.

The most simple-minded way would be to create scripts to recreate the empty tables, create the history tables as select whatever from the original table, delete the unwanted rows, export the table, drop and recreate and import it. But there probably is a better way for your exact situation, especially with a latter-day version or the partitioning option.

jg

--
@home.com is bogus.
http://en.wikipedia.org/wiki/Dumfries_and_Galloway