Re: Best way to delete million records

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: [Info-ingres] Table partitioning questions
    ... modify resultz to btree on cid, trid /*The original key structure*/ with partition=(hash on cid,trid 10 partitions ( ... I expected that I'd end up with 10 physical files and that the table ... I though that by naming the partitions I should be able to separately ... modify each of them, ...
    (comp.databases.ingres)
  • Re: raid-one
    ... It's a common question that people will ask whether they can modify ... them will warn about losing data as part of the operation. ... Not everybody partitions a drive before installing to it, ... I will add a place where they must show this basic knowledge or not try to do raid-1. ...
    (Fedora)
  • Re: raid-one
    ... Remember that when you write instructions, it's people who know little, ... It's a common question that people will ask whether they can modify ... them will warn about losing data as part of the operation. ... Not everybody partitions a drive before installing to it, ...
    (Fedora)
  • [Info-ingres] Table partitioning questions
    ... The table name resultz I ... I though that by naming the partitions I should be able to separately ... eg modify resultz_pp1 to btree on cid, ... Random Duckman Quote #102: ...
    (comp.databases.ingres)
  • Re: install SuSE 9.0 onto a multi-partition drive with Windows, RedHat, BSD and Solaris
    ... I have made ext3 partitions under SUSE ... > That's because they attempt to parse the bootloader file and modify it ... Use only one set of tools to modify the ...
    (alt.os.linux.suse)