Throughout all applications, there are many areas where we enforce basic referential integrity, but various applications handle differently the scenarios when it comes to deleting the child records in the event of the parent record delete. Oracle, like many other databases, offers referential integrity with on delete cascade option. When child tables are created with foreign keys that have the ‘on delete cascade’ option, then deletion of parent record will automatically delete the records in all child tables. The other option to delete the child table records is to, explicitly delete the records in all child tables. This article discusses the POC of both approaches. Oracle run time stats have been gathered for both approaches. If you want to try please use sqlplus.
POC Sample (Cascade Delete vs manual deletion of child table records)
- parent = parent
- children = child1, child2, child2.
- Tests conducted on exadata, version = Oracle enterprise version, 220.127.116.11.0 - 64bit
No On Cascade Delete Option
Without using ‘on delete cascade’ option, the application needs to perform a explicit delete of all child tables.
On Cascade Delete Option
Using this deletion option, need to specify ‘on delete cascade’ when creating the constraint for each child table.
Once the constraints with ‘on delete cascade’ option is in place, deleting the parent record while automatically remove the child record.
|Oracle Stat||No Cascade Delete||Cascade Delete|
|Total Recursive Calls||426||363|
|Total db block gets||19||19|
|Total undo blocks||3||1|
- There is no performance penalty using the ‘On delete cascade’. Oracle stats perform well on all areas with ‘Cascade Delete’ option.
- There is a significant improvement of undo tablespace usage when using the ‘Cascade Deletion’ option. Oracle is using at least 1 block for undo associated with each DML statement. However, when deletes are implicit using cascade deletes, the undo usage for all deletes is just 1 undo block. This is an important observation because when benchmarking the results of deletes, undo and redo are perhaps the most important stats.
- Using ‘On Delete Cascade’ , the application code (data models) is very easy to maintain. The purge scripts are also easier to maintain. We don’t need to track the order of the child table deletes. Importantly, when table model change or new child tables are created, the application code and purge scripts do not need to updated.