I am recently working on the Hierarical data. My requirement is to get the 1st child and his 1st parent. followed by 2nd parent and so on till I reached at the top.
In my data
1. there is cycle .
2. there is multiple hierarchies .
3. a child can have multiple parents and the a parent can have multiple childs.
c2 p2 and so on...
By using connect by and connect_by_root or Recursive with clause I am able to get the desired out put and the data returns very fast. But this is intoducing lot of duplicates. for example my source has 100,000 records the outbout is resulting into millions, say 13 millions and still counting.
I tried distinct but it is not resulting any output after hours of running.
tried other approached to remove duplicates but none of them is resulting any output after running for hours.
Could you please suggest if there is any alternative to but this in a procedure or simple SQL to avoid the huge generation of duplicates.