title: How to improve deletion times in Oracle for a self-referencing table
body: <p>In our Oracle 11g database we have a table, that has a primary key I_Node (int) and also a column called I_Parent_Node (int) that references back to another record in the same table.  The root node has I_Parent_Node = null.  In this way we form a tree structure of nodes, leaves, branches, whatever you want to call them.</p>
  
  <p>Frequently we need to delete an entire branch of nodes at once, meaning a node and all of its  children.  At times this is many, many records, say 50,000 or more.  Since a cascade delete is not allowed on a self-referencing table, we are forced to delete one by one starting with the leaves and working our way back up the tree.  We have experienced hours-long delete times.</p>
  
  <p>We are considering doing a "marking for deletion" technique, where a separate program would clean out the nodes marked for deletion during off-peak hours, but I am interested in whether a database design change or some other Oracle construct could help out here.  I am not trained in Oracle aside from what I've learned on the job, and the people who created the database did not have such large quantities in mind.  I am open to database design changes since it is not yet a fixed design.</p>