Re: Best way to delete million records

From: joel garry <joel-garry_at_home.com>
Date: Mon, 30 Jun 2008 11:05:43 -0700 (PDT)
Message-ID: <0d87f478-6783-4339-b696-9a8601aeb5e0@i36g2000prf.googlegroups.com>


On Jun 30, 1:00 am, fefe78 <pe..._at_puppurnazzi.com> wrote:
> Hi all,
>
> 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.
>
> Thanks for help.
>
> Bye

Xho's answer is the one that immediately comes to mind, how long do you have to do this, and how many rows are you not deleting?

Sometimes when you calculate all the costs and show all the possible ways to do it, 7/24 uptime becomes a nice-to-have rather than the absolute directive.

Another possibility may be to split table in two, replacing it with a view, and slowly move the rows to the archive table and truncate. This may have some bad performance implications, depending on how the table is accessed.

You may very well need to rebuild/coalesce indices or move stuff around in the table, if you are deleting a large proportion of the rows.

Your options are version-dependent.

jg

--
@home.com is bogus.
"If the wings are traveling faster than the fuselage, it's probably a
helicopter - and therefore, unsafe."
Received on Mon Jun 30 2008 - 13:05:43 CDT

Original text of this message