title: Hierarchy/Recursive query on multiple tables
body: <p>We have a requirement where we need to fetch complete hierarchy of records based on Parent Id. We tried with left outer join but the performance going for a toss and the query became huge.</p>
  <p>Oracle version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0</p>
  <p>Say, Product is made up of combination of children, say child1, child2.. child5. And the relation we maintain here is:</p>
  <pre><code>Product can be direct parent of child1, child2, child3, child4.
  child1 can be direct parent of child2, child3 only.
  child2 can be direct parent of child3, child4.
  child3 can be direct parent of child4.
  </code></pre>
  <p>To make it simple have provided the data with 3 tables. _Imed_Par = Immediate parent of child/sub product<a href="https://i.stack.imgur.com/IRqil.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IRqil.jpg" alt="enter image description here" /></a></p>
  <pre><code>Table1                 Table2           Table3
  Product ProductName    SPB   P_ID       SPC    SP_B_ID  P_ID
  P101     Pname1        B201  P101       C301            P101
  P102     Pname2        B202  P103       C302    B201    P101
  P103     Pname3        B203  P103       C303    B202    P103
                         B204  P101       C304    B203    P103
                                          C305    B202    P103
     Expected Result:                             
      P_ID    SP_B_ID  SP_C_ID    Imed_PAR                
      P101    B201     C302       SPB             
      P101    B204                Product                 
      P101             C301       Product     
      
  
  
   --- GETTING FIRST LEVEL ---
  select A,B,C, 'PRODUCT' from PRODUCT PR   
  left outer join SPB B on B.PRODUCT_id=PR.id
  left outer join SPC C on C.PRODUCT_id=PR.id AND C.SPB_ID IS NULL
  left outer join SPD D on D.PRODUCT_id=PR.id AND D.SPB_ID IS NULL AND D.SPC_ID IS NULL
  LEFT OUTER JOIN SPE E ON E.PRODUCT_ID=PR.id AND E.SPC_ID IS NULL AND E.SPD_ID IS NULL
  
  UNION ALL
  
  --- GETTING RECORDS OF ALL CHILDS WHOSE IMMEDIATE PARENT IS SPB ---
  select A,B,C, 'SPB' from SPB B
  left outer join SPC C on C.SPB_id=B.id
  left outer join SPD D on D.SPB_id = B.id and D.SPC_id is null
  --NO SPD JOIN HERE AS THERE IS NO DIRECT AND RELATION SHIP---
  
  UNION ALL
  
  SELECT A,B,C, 'SPC' FROM SPC C
  left outer join SPD D on D.SPC_ID=C.id AND D.SPB_ID IS NULL 
  LEFT OUTER JOIN SPE E ON E.SPC_ID=C.id AND E.SPD_ID IS NULL 
  
  UNION ALL
  
  SELECT A,B,C, 'SPD' FROM SPD D
  LEFT OUTER JOIN SPE E ON E.SPD_ID=D.id AND E.SPC_ID IS NULL 
  </code></pre>