Hello Gurus,
How to remove circular dependency if data is arrange in parent-child relationship without using Hierarchical query.
I have below table with data.
CREATE TABLE tab1 (
id NUMBER,
parent_id NUMBER
);
1
2
3
4
9
5
6
7
8
20
21
23
24
25
26
30
31
INSERT INTO tab1 VALUES (1, NULL);
INSERT INTO tab1 VALUES (2, 1);
INSERT INTO tab1 VALUES (3, 2);
INSERT INTO tab1 VALUES (4, 3);
INSERT INTO tab1 VALUES (9, 4);
INSERT INTO tab1 VALUES (5, 1);
INSERT INTO tab1 VALUES (6, 5);
INSERT INTO tab1 VALUES (7, 5);
INSERT INTO tab1 VALUES (8, 1);
INSERT INTO tab1 VALUES (20, null);
INSERT INTO tab1 VALUES (21, 20);
INSERT INTO tab1 VALUES (23, 20);
INSERT INTO tab1 VALUES (24, 23);
INSERT INTO tab1 VALUES (25, 24);
INSERT INTO tab1 VALUES (26, 20);
INSERT INTO tab1 VALUES (30, null);
INSERT INTO tab1 VALUES (31, null);
Adding data to create circular dependency.
a) 1-2-3-4-9-1
INSERT INTO tab1 VALUES (1, 9);
b) 1-5-6
INSERT INTO tab1 VALUES (6, 1);
c) 23-24-25
INSERT INTO tab1 VALUES (23, 25);
d) 1-8
INSERT INTO tab1 VALUES (1, 8);
Data inside table as below (Row no is just psuedocolumn just to give sequence no not available in table)
Row No child parent
------ ----- ------
1 1 null
2 2 1
3 3 2
4 4 3
5 9 4
6 5 1
7 6 5
8 7 5
9 8 1
10 20 null
11 21 20
12 23 20
13 24 23
14 25 24
15 26 20
16 30 null
17 31 null
18 1 9 -- This row is added to create circular dependency 1-2-3-4-9-1
19 6 1 -- This row is added to create circular dependency 1-5-6
20 23 25 -- This row is added to create circular dependency 23-24-25
21 23 25 -- This row is added to create circular dependency 1-8
If there is circular dependency among 1-2-3-4-9-1 then Row#18 has to be deleted.
If there is circular dependency among 1-5-6 then Row#19 has to be deleted.
If there is circular dependency among 1-8 then Row#21 has to be deleted.
Similarly,
If there is circular dependency among 23-24-25 then Row#20 has to be deleted.
If the data is huge and circular dependency is present in multiple level and Hierarchical query used then it goes
into infinite loop .
So rather using Hierarchical query Is there any option which remove data itself which causes circular reference.
Regards
Ram