Oracle层级查询：如何获取指定节点的特定类型父组织记录？

我们有如下组织结构的表定义与测试数据：

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

insert into orgs values ( 1, '总裁办', 'PRES', null );
insert into orgs values ( 2, '信息技术部', 'DEP', 1 );
insert into orgs values ( 3, '软件开发分部', 'DIV', 2 );
insert into orgs values ( 4, '数据库组', 'UNIT', 3 );
insert into orgs values ( 5, '开发组', 'UNIT', 3 );
insert into orgs values ( 6, '基础设施部', 'DEP', 1 );
insert into orgs values ( 7, '安全分部', 'DIV', 6 );
insert into orgs values ( 8, '系统管理员分部', '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

当前需求：获取org_id为4（数据库组）对应的org_type为DEP的上级组织（即信息技术部），目前通过循环函数实现存在性能问题，需要高效的SQL查询方案。

方案1：反向递归筛选（推荐） #

利用Oracle层次查询特性，从目标节点向上遍历所有上级，筛选出部门类型（DEP）的组织，并取距离目标最近的一条：

select org_id, org_name, org_type
from (
    -- 从目标节点向上遍历所有上级，记录节点层级
    select 
        org_id, 
        org_name, 
        org_type, 
        level as node_level
    from orgs
    connect by prior parent_org_id = org_id  -- 反向递归：当前节点的父ID是上一层节点的ID
    start with org_id = 4  -- 起始节点为org_id=4
)
where org_type = 'DEP'  -- 筛选部门类型组织
order by node_level asc  -- 按层级升序，取最接近目标的部门
fetch first 1 row only;  -- 返回唯一结果

方案2：递归终止优化 #

如果希望递归过程中找到目标就停止，可使用以下逻辑进一步提升性能（适合层级较深的场景）：

select org_id, org_name, org_type
from orgs
connect by prior parent_org_id = org_id
start with org_id = 4
and org_type != 'DEP'  -- 起始节点非DEP时才递归
union all
-- 处理起始节点本身就是DEP的边界情况
select org_id, org_name, org_type
from orgs
where org_id = 4 and org_type = 'DEP'
fetch first 1 row only;

• 为parent_org_id、org_id和org_type组合创建索引，可大幅提升层次查询速度：

  create index idx_orgs_hierarchy on orgs(parent_org_id, org_id, org_type);
  

• 相比循环函数，单条SQL避免了多次数据库调用与上下文切换，Oracle层次查询引擎会自动优化执行计划。

内容的提问来源于stack exchange，提问作者ads