Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Suggestions on denormalizing a hierarchical structure.

SaubhikDec 7 2022 — edited Dec 7 2022

I need some suggestions and best approach to de-normalize a hierarchical structure. For the time being I am using EMP and DEPT tables in SCOTT schema. I might post the actual or near actual example after getting some inputs from this thread.
Now, I want to de-normalize EMP and DEPT to maximum possible level fro my data warehouse.

   SELECT e.ename                                 "Employee",
e.deptno,
   d.dname,
   level,
   sys_connect_by_path(e.ename, '/')       "Manager Higherarchy"
FROM
        emp e
   INNER JOIN dept d ON ( e.deptno = d.deptno )
START WITH
   ename = 'KING' -- Hardcoding because I know King is the King.. but it will not be in real time
CONNECT BY NOCYCLE PRIOR empno = mgr
                  AND level <= 10 -- A huger numver
ORDER BY
   "Employee",
   level,
   "Manager Higherarchy"; 
  

It gives me something like :
image.png
The problem is I can't create a table like EMPNO, ENAME, DEPTNO, DNAME, LEVLE1_MGR_NAME, LEVEL2MRG_NAME ...... etc
Because I don't know where it ends. My question is what is thew best possible approach to de-normalize this.

Comments
Post Details
Added on Dec 7 2022
6 comments
491 views