0 Replies Latest reply on Nov 28, 2013 9:09 AM by success_me

    Hierarchical data in Oracle


      Hi All,


      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.


      sample data

      child  parent

      c1     p1

      c2     p1

      p1     p2

      p1     p3

      p3     c1


      Output required

      c1        p1

      c1          p2

      c1         p3

      c2          p1

      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.


      Please help.