3

I have a very large Oracle database, with many many tables and millions of rows. I need to delete some of the rows end-to-end. By end-to-end, I mean, if I want to delete a row from a table then I must delete all the corresponding records in child tables first and ensure that deletion happens without any constraint violation errors while deleting the parent table entry.

As an example:

Table A defines dependency over Table B and Table C (means Table A has foreign key constraint on Table B and Table C).
Table B defines dependency over Table C and Table D.

I want do delete rows in this specific order something like this:

Table A<=Table B<=Table C<=Table D

I have come up with two approaches:

  1. Using a service API which takes parent table rowID and archives whole data in JSON format and keeps on deleting the entry using cascade option.

  2. Other option is to use delete-with-cascade along with triggers to save the deleted values.

Once data is fetched, I want to better put it in similar archived tables Table_A_archived, Table_B_archived etc or If it could be fetched in file, that is fine too.

Any thoughts on initial approaches will be really valuable. If anyone has done this sort of thing before, it will be great to learn from their experience.

Just FYI, this is a live database and I can't do absurd things (I can obviously test it before pushing it to prod :)). Also, deletion/archival is not just on the basis of time.

Thanks in advance!

Improve this question
5
  • Do I understand you correctly that you want to move a interrelated set of entities from one set of tables to a different set of tables, while keeping the relations intact?
    – Bart van Ingen Schenau
    Nov 5, 2014 at 7:26
  • Is it correct that you haven't decided if you are going to archive the deleted data in a file or a set of archive tables? You should really make that decision first, depending on why you are going to archive the data (which you did not tell us so far).
    – Doc Brown
    Nov 5, 2014 at 8:27
  • 1. Yes, you got it right. 2. Primary reason is to reduce the DB size but secondarily it could be used for different purposes which are yet not decided (not that important though).
    – instanceOfObject
    Nov 5, 2014 at 17:52
  • @instanceOfObject: since you did not adress me in your comment, I did not see it in my inbox.
    – Doc Brown
    Nov 6, 2014 at 15:55
  • @BartvanIngenSchenau tagging!
    – instanceOfObject
    Nov 6, 2014 at 19:16

2 Answers 2

Reset to default
2

Moving the data to archive tables will reduce the amount of data in the "live tables", but it won't reduce your overall database size. So if that is your goal, use files, which can be compressed easily if necessary.

However, if you want to be able to query your archive data later on, and database size is not really your problem, the archive tables may be the better choice.

The usage of triggers makes IMHO only sense if your archiving process has to happen synchronously during your production process (for example, whenever a DELETE command is issued to your data in the normal production, you want immediately the related records to be archived). On the other hand, if your archiving process is designed to be an asynchronous process, maybe executed at after-work hours, with a time-stamp filter for identifying the data to be archived, there is no need to use triggers. Instead, you can design your archive process using some bulk deletes, which can be significantly faster than deleting one record after another.

Improve this answer
2
  • I will be later moving this data to other storage. Also, by size of the DB, I mean size of live DB (to ease the process of re-storation, when needed). I don't plan to do it in sync fashion but my records are on the basis of parent table's status than the timestamp so, bulk deletes might not be of much help. In otherwise case (without triggers), I think I will have to find the dependencies (using reflection), create a dependency graph and perform ordered archival and deletion which sounds like little complex than the trigger approach. Any thoughts?
    – instanceOfObject
    Nov 6, 2014 at 19:13
  • @instanceOfObject: well, I don't know your data model, and if you think triggers make things easier, then don't hesitate to use them. If you have so many tables you need to auto detect your dependencies, the problem may be more complex than I thought. But I guess it would not mind make some performance tests first.
    – Doc Brown
    Nov 6, 2014 at 19:49
2

Use archive tables, and use a trigger to archive the record into the archive table. These triggers can be generated programmatically from the table definition.

Then you can just delete the records with the cascade option, and know that the archive is being created automatically.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.