Forum Stats

  • 3,752,275 Users
  • 2,250,483 Discussions
  • 7,867,774 Comments

Discussions

What is the best way to delete millions of row from large table?

13»

Answers

  • Unknown
    edited Jul 23, 2018 1:15PM
    what is the best way to delete rows from this table?

    That depends - how many rows are being deleted? how many rows are in the table now?

    Simple way 'delete * from myTable where ....'

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Jul 23, 2018 4:50PM
    Sevdimali Isayev wrote:I think they will accept, one downtime for a life for this situation. 

    Whats your full version number? If you’re on at least 12.2.0.1 then you can partition tables online using  an alter table statement. Below that then you’d want to use dbms_redefinition.

    BTW why do you need to delete the same number of rows that you insert per day in one go? You could just have a job that runs each hour that deletes rows that need to be deleted (assuming the deletion is based on the age of the data and you can handle the granularity of an hour). Alternatively, what even is the problem with just a straight delete statement? It shouldn’t matter how long it takes - it’s only touching data you apparently don’t care about so nothing should be effected, right?

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited Jul 24, 2018 11:47AM

    All, just a reminder that partitioning is an extra-cost option that most shops do not license.

    - -

    HTH -- Mark D Powell --

This discussion has been closed.