SEO

#1 (**permalink**) 02-24-2008, 05:30 AM

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)

#2 (**permalink**) 02-24-2008, 05:31 AM

On Wed, 30 Jun 2004 15:21:04 +0100, "Mill" <mill.h@ntlworld.com>
wrote:

>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)
>
>
How 'bout just partitioning customer_orders on order date? Or perhaps
a combination of order date and order status?

#3 (**permalink**) 02-24-2008, 05:32 AM

"Mill" <mill.h@ntlworld.com> wrote in message news:<hSAEc.540$TK1.188@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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Archiving Large Tables