我们有一个需求，需要根据父id获取完整的记录层次结构。我们尝试使用left outer join，但性能受到影响，查询变得非常庞大。
oracle版本：oracle database 19c enterprise edition release 19.0.0.0.0
比如说，产品是由儿童组合而成的，比如说儿童1，儿童2。。孩子5。我们这里的关系是：

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.

为了简单起见，我们提供了三个表_imed\u par=子产品/子产品的直接父母

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