As per the requirement i have to identified data from table t1. Once the data is identified i need to identify its child records as well.
I could easily identify its childs if there is only one self referential constraint.
select * from t1 start with c1=100 connect by prior c1=c2;
But My table has multiple self referential integrity constraints like below
create table t1( c1 number pk, c2 number references c1, c3 number references c1, c4 number references c1);
i have tried to identify the childs of each self RI separately. But the problem here is after identifying the childs i have to delete all the childs and parent records.
With the approach of identifying the self referential Integrity separately we may find one record is child in one self RI the same record might be parent in other Self RI. so while deleting we are facing child record found issue.
so i have used the below query to identify all the childs in one shot. But the query is running for hours and not getting finished.
select * from t1 start with c1=100 connect by prior c1=c2 or prior c1=c3 or prior c1=c4;
i thought of using the below query
select * from t1 start with c1=100 connect by prior c1=c2 union select * from t1 start with c1=100 connect by prior c1=c3 union select * from t1 start with c1=100 connect by prior c1=c4;
But after identifying the childs i have delete all identified data. while deleting in bulk mode im getting errors like child records found. Upon debug we identified with this approach a record might be parent in one self RI and child in another self RI. so we have to build tree with all constraints . Please let me know if i missing something
We need to move this data to another db before deleting . All the identified rows will have to be moved and then purged.
Please suggest any way to identify all child records to be moved and purged.