Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2016
    Posts
    7

    Unanswered: Delete CASCADE or Set Nul ?

    Hello everybody

    I would be interested to know your experience on the implementation of the "delete cascade" on tables with very high volumetry and/or lot of indexes
    The question is: have you ever encountered "deadlock" related to cascading delete in the underlying tables.
    Do you recommand the "delete set nul" option (an batch deletion at off-peak hours) in the case of tables with very high volumetry or whose underlying tree is important ?
    In advance thank you for your feedback

  2. #2
    Join Date
    Oct 2007
    Posts
    155
    Provided Answers: 9
    do you have indexes that exactly match the defined key? That is the typical problem when someone is having an issue with a cascading delete.

  3. #3
    Join Date
    Dec 2016
    Posts
    7
    Quote Originally Posted by DNance View Post
    do you have indexes that exactly match the defined key? That is the typical problem when someone is having an issue with a cascading delete.
    Thank's for your answer

    The problem I am talking about here is not situation where no eligible index is found for the operation, but the considerable number of lines affected by the cascade, when the depth is important (several levels of tables, each of them having several indexes)
    Removing a few thousand rows from the reference table can lead to the removal of hundreds of millions of rows across all the tables and indexes that refer to it, and this is where contention and time-out problems may occur.
    The cascade suppression is immediate, whereas the use of "SET NULL" makes it possible to postpone the physical suppression in a low activity range, at night or the week end, and thus avoid the restraints
    The question is how to know from what suppression volume it is no longer reasonable to proceed with real time deletion, and it is better to delay this operation with the "SET NULL" option and then a batch processing.

  4. #4
    Join Date
    Oct 2007
    Posts
    155
    Provided Answers: 9
    Maybe not a good idea to have RI on a set like that. You could enforce it programmatically. Then on some type of scheduled basis do a REORG with discard, where this key is not in the parent?

  5. #5
    Join Date
    Dec 2016
    Posts
    7
    Quote Originally Posted by DNance View Post
    Maybe not a good idea to have RI on a set like that. You could enforce it programmatically. Then on some type of scheduled basis do a REORG with discard, where this key is not in the parent?
    That a very bad idea, there is no application that can guarantee integrity in a competitive context of mutli-user access, only the database can do so !
    Applications do not know how to handle locks, commits, and rollbacks, and the logging on which they rely.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •