Re: Best way to delete million records

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...@xxxxxxxxxxxxxxx> 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
.

Relevant Pages

  • Re: Partitioning Question
    ... I have usually used ubuntus automated partitions but Im wanting to do a bit more. ... I have read on the forums about some sizes but I wanted to run this by those who would know: ...
    (Ubuntu)
  • Re: Installing Linux
    ... Thanx a lot for the prompt reply. ... Just having a little problem with my partitions. ... It's usually best to search for an answer on the distro's own forums, ... In the case of Fedora, you need their forum, and they have a special ...
    (comp.os.linux.misc)