Re: Best way to delete million records
From: fefe78 <peppo_at_puppurnazzi.com>
Date: Mon, 30 Jun 2008 10:58:13 +0200
Message-ID: <g4a774$k77$1@aioe.org>
>
> If you do not have proper indexes in place not matter what you need to
> at least once run through the whole table. If this is a task you face
> regularly I'd probably bite the bullet and create appropriate indexes
> or - even better - partitions. If this is just done on few occasions
> it might be better to just issue the DELETE and wait. Watch out for
> undo / redo sizes though.
>
> Kind regards
>
> robert
Received on Mon Jun 30 2008 - 03:58:13 CDT
Date: Mon, 30 Jun 2008 10:58:13 +0200
Message-ID: <g4a774$k77$1@aioe.org>
The table is indexed, and this is a one time procedure.
After this will be scheduled a job to daily delete old records.
In some forums I red that drop indexes and ricreate them after deletion,
could be a good solution.
Robert Klemme ha scritto:
> On Jun 30, 10:00 am, fefe78 <pe..._at_puppurnazzi.com> wrote:
>> I have to delete about 43 millions rows from a table, but the following >> conditions exist: >> >> - The table is not partitioned and unfortunately I can't modify the >> structure >> - The delete procedure must be done without stopping service, so I can't >> use CTAS, moving records and renaming table. >> - I can't use truncate because the records affected are selected in a >> range of dates >> >> Waiting your suggestions and probable problems for this procedure.
>
> If you do not have proper indexes in place not matter what you need to
> at least once run through the whole table. If this is a task you face
> regularly I'd probably bite the bullet and create appropriate indexes
> or - even better - partitions. If this is just done on few occasions
> it might be better to just issue the DELETE and wait. Watch out for
> undo / redo sizes though.
>
> Kind regards
>
> robert
Received on Mon Jun 30 2008 - 03:58:13 CDT