Oracle 19c中CONNECT BY无START WITH结合GROUP BY非空唯一列时GROUP BY失效问题咨询

你好，我来帮你梳理和分析这个遇到的Oracle 19c的奇怪问题。从你提供的测试代码和描述来看，核心问题是：当使用CONNECT BY但不指定START WITH子句，同时结合GROUP BY时，因为表中存在一个非空且带唯一约束的列（name），Oracle优化器错误地跳过了GROUP BY的去重逻辑，导致原本应该被合并的重复id行保留了下来。

先复现你的测试场景 #

首先是你搭建测试环境的完整代码：

create table test_hie (id int, parent int, name varchar2(64) not null);
insert into test_hie (id, parent, name) values (0, null, 'ABC');
insert into test_hie (id, parent, name) values (1, 0, 'DEF');
create unique index test_hie_idx_name on test_hie (name);

alter session set statistics_level = all;

然后是你执行的关键查询——期望通过GROUP BY id来去掉CONNECT BY产生的重复id：

select id from test_hie connect by prior id = parent group by id;

你还通过执行计划来排查问题：

select * from table(dbms_xplan.display_cursor('6pfqf6fg5crck', 0, 'ALLSTATS LAST PROJECTION'));

问题原因分析 #

这个现象本质是Oracle优化器的一个特殊推断行为：当表中存在一个非空的唯一约束/索引时，优化器会默认认为表中的每一行都是绝对唯一的，甚至在CONNECT BY（无START WITH）会生成多路径重复行的场景下，错误地判断GROUP BY id不会改变结果集，因此直接跳过了GROUP BY的执行步骤。

简单来说，优化器被唯一约束“误导”了——它只看到了表本身的行唯一性，却忽略了CONNECT BY会生成额外的重复行这个事实。

验证与解决思路 #

给你几个可行的验证和解决方向：

• 验证问题本质：先删除那个唯一索引test_hie_idx_name，再执行同样的GROUP BY查询，你会发现GROUP BY能正常工作，重复的id会被合并。对比前后的执行计划，就能看到优化器在有无唯一索引时的执行逻辑差异。
• 强制优化器执行GROUP BY：在查询中添加优化器提示，阻止它使用唯一索引的推断，比如：

  select /*+ NO_UNIQUE_SCAN(test_hie) */ id from test_hie connect by prior id = parent group by id;
  

• 用DISTINCT替代GROUP BY：因为DISTINCT的优化逻辑和GROUP BY不同，可能不会被优化器跳过，尝试：

  select distinct id from test_hie connect by prior id = parent;
  

• 添加START WITH子句（业务允许的话）：如果你的业务场景有明确的根节点，比如parent is null，直接加上START WITH parent is null，这样CONNECT BY只会生成合法的层次路径，不会产生多余的重复行，也就不需要依赖GROUP BY去重了：

  select id from test_hie start with parent is null connect by prior id = parent;
  

备注：内容来源于stack exchange，提问作者ABu