Oracle Hierarchy – How to get a particular level record in a query?

We have the following structure for Organizations.

create table orgs (
    org_id number
  , org_name varchar2(250)
  , org_type varchar2(10)
  , parent_org_id number 
)
/

insert into orgs values ( 1, 'President', 'PRES', null );
insert into orgs values ( 2, 'Information Technology Department', 'DEP', 1 );
insert into orgs values ( 3, 'Software Development Division', 'DIV', 2 );
insert into orgs values ( 4, 'Database Unit', 'UNIT', 3 );
insert into orgs values ( 5, 'Developer Unit', 'UNIT', 3 );
insert into orgs values ( 6, 'Infrastracture Department', 'DEP', 1 );
insert into orgs values ( 7, 'Security Division', 'DIV', 6 );
insert into orgs values ( 8, 'System Admintrator Division', 'UNIT', 7 );


  select level, org_id, org_name, org_type
    from orgs
 connect
      by 
   prior org_id = parent_org_id
   start
    with parent_org_id is null

Query returns the result

What I’m trying to do is let’s say I need to get the Department (Information Technology Department) of org_id 4 (Database Unit), how should I build the query for that?

Currently we built a function that returns the department id which basically loops until it reaches the DEP parent. But it has a performance issue.

Answer

You can invert the hierarchy query to start from the Database Unit (org_id = 4) and filter to the department ancestor (org_type = 'DEP'):

select org_id, org_name
from orgs
where org_type = 'DEP'
connect by prior parent_org_id = org_id
start with org_id = 4;

The where clause is applied after the hierarchy is retrieved.