Deleting multiple child tables from parent in one SQL statement
Dear Expert:
Given the following tables:
create table parent (
id integer primary key,
value varchar2(30));
create table child1 (
id integer primary key,
parent_id references parent);
create table child2 (
id integer primary key,
parent_id references parent);
create table child3 (
id integer primary key,
parent_id references parent);
I know I can create 3 delete statements as shown in note, but I would like to use the multiple statement format so I can run only one Oracle SQL statement. One of the reasons is that I have 10000s of records in both parent and children. Notice that child table may not have entries for the parent value, thus I can not use inner join. Notice that I do simplify my table structure for simplicity sake.
Thanks,
MAS
Note: Typical child deletes:
delete from child1 c1 where c1.parent_id in (select id from parent where value = 'a')
delete from child2 c2 where c2.parent_id in (select id from parent where value = 'a')
delete from child3 c3 where c3.parent_id in (select id from parent where value = 'a')
Given the following tables:
create table parent (
id integer primary key,
value varchar2(30));
create table child1 (
id integer primary key,
parent_id references parent);
create table child2 (
id integer primary key,
parent_id references parent);
create table child3 (
id integer primary key,
parent_id references parent);
I know I can create 3 delete statements as shown in note, but I would like to use the multiple statement format so I can run only one Oracle SQL statement. One of the reasons is that I have 10000s of records in both parent and children. Notice that child table may not have entries for the parent value, thus I can not use inner join. Notice that I do simplify my table structure for simplicity sake.
Thanks,
MAS
Note: Typical child deletes:
delete from child1 c1 where c1.parent_id in (select id from parent where value = 'a')
delete from child2 c2 where c2.parent_id in (select id from parent where value = 'a')
delete from child3 c3 where c3.parent_id in (select id from parent where value = 'a')
if you dont like above,
create a stored procedure
and call this!
create a stored procedure
procedure DelData(p_key in varchar2) is
begin
delete from child1 where parentid=(select id from parent where key=p_key );
delete from child2 where parentid=(select id from parent where key=p_key );
delete from child3 where parentid=(select id from parent where key=p_key );
end;and call this!
ASKER
The idea is to delete both the parent and the children records for the given condition, but we should not repeat the condition on the 3 queries as it may contain 1000s of hits. SP could be OK if no repeating condition is in the SP.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
is this a 1M$ question?
did you check my suggestion @ ID: 42111642
did you check my suggestion @ ID: 42111642
ASKER
Yes that suggestion is not feasible because we want to delete all children related content.
The question is more a performance question. The current solution works OK but it does not scale when the number of records are 1M+.
The question is more a performance question. The current solution works OK but it does not scale when the number of records are 1M+.
1 statement or 4 statements...
I don't think it will matter...
and this is one time process, who cares if it does not scale :)
I don't think it will matter...
and this is one time process, who cares if it does not scale :)
When you establish the foreign key constraint use DELETE CASCADE:
CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent (id)
ON DELETE CASCADE
So when you delete the master (parent) record all records that reference this record will be automatically deleted.
CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent (id)
ON DELETE CASCADE
So when you delete the master (parent) record all records that reference this record will be automatically deleted.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Huseyin: This is one time process that will happen at least 10 to 30 times a day (not every day of course) based on current usage.
@schwertner and @Mark: Interesting approach, have you had any thoughts regarding performance meaning using delete cascade vs delete SQL?. Notice that all deletions are done as part of a state cleanup involving 12 children tables but other web site users may want to use the parent/children table (for fetching updating different values of course) - currently a LINQ based solutions takes 10-15 minutes to do this clean up for very big number of records (500000+)
@schwertner and @Mark: Interesting approach, have you had any thoughts regarding performance meaning using delete cascade vs delete SQL?. Notice that all deletions are done as part of a state cleanup involving 12 children tables but other web site users may want to use the parent/children table (for fetching updating different values of course) - currently a LINQ based solutions takes 10-15 minutes to do this clean up for very big number of records (500000+)
cascade delete is straight forward and no matter what your data will be in consistent shape all the time, no orphan children left and right...
and you dont care about children, when you delete parents, children will be deleted at the same time...
performance! dunno...
if you dont refer to parent when deleting children, you can try 4 deletes
go with this approach, put them in a stored proc and call it every day...
if you need to deal with parent when deleting, it would be pain without cascade delete...
and you dont care about children, when you delete parents, children will be deleted at the same time...
performance! dunno...
if you dont refer to parent when deleting children, you can try 4 deletes
delete from child1 where insDate between trunc(sysdate) and trunc(sysdate)-1
delete from child2 where insDate between trunc(sysdate) and trunc(sysdate)-1
delete from child3 where insDate between trunc(sysdate) and trunc(sysdate)-1
delete from parent where insDate between trunc(sysdate) and trunc(sysdate)-1go with this approach, put them in a stored proc and call it every day...
if you need to deal with parent when deleting, it would be pain without cascade delete...
Cascading deletes don't ensure consistent data. Referential integrity does.
As long as the foreign keys in the children tables are indexed, cascading deletes should have a negligible performance difference. That difference is likely offset by having to run multiple queries to do it in individual statements (you have to keep parsing and generating execution plans).
The biggest thing you need to worry about is application design. If one part of your application relies on the cascading deletes and another relies on individual statements, you will likely hit deadlocks at some point. The locking schemes for the two methods are very different.
Is this just a parent/child relationship, or do the children have children? There used to be very bad performance issues with cascading deletes when the number of levels in the tree to be deleted got above about 5. It has to do with the locking methods and how it traverses the tree of rows that cascading deletes use.
As long as the foreign keys in the children tables are indexed, cascading deletes should have a negligible performance difference. That difference is likely offset by having to run multiple queries to do it in individual statements (you have to keep parsing and generating execution plans).
The biggest thing you need to worry about is application design. If one part of your application relies on the cascading deletes and another relies on individual statements, you will likely hit deadlocks at some point. The locking schemes for the two methods are very different.
Is this just a parent/child relationship, or do the children have children? There used to be very bad performance issues with cascading deletes when the number of levels in the tree to be deleted got above about 5. It has to do with the locking methods and how it traverses the tree of rows that cascading deletes use.
I can't imagine that the manual delete will be faster that cascading delete. Also manual delete should be organized as transaction to ensure ACID, to think what will happen if one DML fail and so on.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The more levels (and/or rows) the more troubles with locks and time. But the asker is speaking about 3 levels. If this is not the case experiments can help to estimate the time. Creating reference constraints and deleting them will not hurt the tables in the schema.
Manual deleting is a special kind of "hardcoding". It should be documented, kept in mind and well known for all DBAs in the full lifecycle of the applications. And we all know that the usual case is to forget about it in a couple of months.
Manual deleting is a special kind of "hardcoding". It should be documented, kept in mind and well known for all DBAs in the full lifecycle of the applications. And we all know that the usual case is to forget about it in a couple of months.
Number of rows was irrelevant. It was purely number of levels. As the question was about performance, it seems prudent to mention that there is a performance degradation with cascading deletes. It is internal to Oracle and there isn't anything you can do to improve that performance. In fact, changing the one delete to two or three deletes to delete parts of the tree at a time resulted in significant performance improvement.
I read the askers post: "I know I can create 3 delete statements as shown in note, but I would like to use the multiple statement format so I can run only one Oracle SQL statement. ". It is concentrated on simplicity, not on performance. But all you write is very good analysis and warning because of your very reach experience and long years work with Oracle.
And I read the asker's comment.
The question is more a performance question.and in asker's comment
have you had any thoughts regarding performance meaning using delete cascade vs delete SQLBased on the asker's comments, they are looking for performance, not feasibility. So, based on what the asker has said, they seem to be looking for performance differences. I guess I'm wrong though.
ASKER
Performance is my main concern and most of your comments provided great insights. I am planning to go for the statements and transactions as I would have more control lock wise. In my case there are 2 levels: one parent and 12 child tables, they are part of a 4 level hierarchy but for the feature we only need to deal with the parent/child level only.
Note: Make a single statement was a colleague suggestion, it was only one possible approach.
Note: Make a single statement was a colleague suggestion, it was only one possible approach.
if performance is the main concern, get a UAT version of your production environment (same/similar size data) and do some testing...
try both
* multiple deletes
* cascade deletes
* combination / mixed of above
you may also try
- add a update lock to table
- disable all triggers if it is not needed
- delete records
- enable trigger
- unlock table
then decide what to do...
try both
* multiple deletes
* cascade deletes
* combination / mixed of above
you may also try
- add a update lock to table
- disable all triggers if it is not needed
- delete records
- enable trigger
- unlock table
then decide what to do...
then you just need
Open in new window
no fuss no mess no orphaned children left around :)
https://www.techonthenet.com/oracle/foreign_keys/foreign_delete.php