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!

best way to store and retrieve hierarchical data in efficient way. Thanks in advance

TP0692Mar 26 2019 — edited Mar 26 2019

I would like to store multiple levels of org hierarchies in a table. I would like to know is there any best way to store and retrieve it without recursively call?. I came across the self-referencing table way. I would like to will it be performance optimized?

Example

SQL> create table hierarchic_tbl (

id number primary key,

parent_id references hierarchic_tbl, -- references itself

descr varchar2(20),

active number(1) not null check (active in (0,1))

);

SQL >

-- 1st level:

insert into hierarchic_tbl values ( 1, null, 'Language' , 1);

-- 2nd level:

insert into hierarchic_tbl values ( 2, 1, 'VB' , 1);

insert into hierarchic_tbl values ( 3, 1, 'C#' , 1);

insert into hierarchic_tbl values ( 4, 1, 'JAVA', 1);

-- 3rd level (below VB)

insert into hierarchic_tbl values ( 5, 2, 'VB6.0' , 1);

insert into hierarchic_tbl values ( 6, 2, 'VB.NET' , 1);

-- 4th level (below VB.NET)

insert into hierarchic_tbl values (7, 6, 'VB.NET2003' , 1);

insert into hierarchic_tbl values (8, 6, 'VB.NET2005' , 1);

I want to know. How the above design affects the large level hierarchy at the performance level. Want to know the best way to query the data and display it in flattened