Re: Best way to delete million records

From: Robert Klemme <>
Date: Mon, 30 Jun 2008 01:45:46 -0700 (PDT)
Message-ID: <>

On Jun 30, 10:00 am, fefe78 <> 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:45:46 CDT

Original text of this message