How to improve deletion times in Oracle for a self-referencing table

Question

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.

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.

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.

Answer 1

You may want to consider separating the hierarchy structure from the main table. So you main table would just have primary ids (lets call it "ID"), and your hierarchy table would have "ID, ParentID, TreeID". ParentID is that ID's parent node, and TreeID is the highest parent in the tree (level 1).

So, a level 1 node would look like:

ID, ParentID, TreeID 
1, [null], 1

A level 2 node would look like:

ID, ParentID, TreeID
2, 1, 1

A level 3 node would look like:

ID, ParentID, TreeID
3, 2, 1

And so on.

You would use Oracle hierarchy queries (Connect by queries) to query or traverse the trees. This table will be very thin (not many columns, these 3 + some modified dates maybe), so updating these relationships should be much faster and scale better than messing with the main table.

Answer 2

You should be able to do this with deferrable constraints and a hierarchical query.

If your foreign key constraint (on I_Parent_Node) is not already deferrable, drop it and recreate it with the keyword "DEFERRABLE".

Here's an example using the EMPLOYEES table from Oracle's examples (I modified the DEPARTMENTS table too so that this would execute, that's really not needed for an example though):

Drop & Recreate your foreign key if it's not currently deferrable:

alter table employees drop constraint emp_manager_fk;
alter table employees add constraint emp_manager_fk foreign key (manager_id) references employees(employee_id) deferrable;

In your transaction, defer your contraints, and delete using a hierarchical query:

set constraints all deferred; 

delete
from     employees e
where    employee_id in (select   employee_id
                         from     employees
                         start with employee_id = 108
                         connect by prior employee_id = manager_id);

The "108" is the ID of my "parent" record.

Answer 3

I assume you've already done standard tuning - i.e. are the node and parent node ID columns suitable indexed?

(1) One approach to the problem is to use PL/SQL. Bulk collect the IDs to be deleted, using a hierarchical query that returns the leaf rows first, into an array; then do a bulk delete (FORALL) using the array.

(2) Another approach is a soft-delete - mark the rows as "deleted", but never actually delete them. You would need to modify your application (or use Oracle VPD to automatically omit the "deleted" rows from queries). This might work reasonably well if deleting a node is relatively rare; but if you're routinely deleting lots of nodes then this would clutter the table with a lot of old data.