Oracle中CONNECT BY LEVEL正则拆分查询挂起及正则匹配问题

一、CONNECT BY LEVEL逐行提取匹配项时查询挂起 #

故障原因 #

核心触发点有两个：

• 未加递归循环阻断条件：单表使用CONNECT BY做层级展开时，如果没有父子行唯一值关联判定，Oracle会对已生成的行重复递归，生成指数级增长的冗余数据，最终耗尽资源挂起，这是Oracle用CONNECT BY拆分字符串的高频坑。
• 正则使用.*贪婪匹配：长文本场景下贪婪匹配会产生大量回溯计算，进一步放大递归的性能问题。

修复方案 #

1. 优化正则逻辑：将virtualDomains\..*\"改为virtualDomains\.[^"]+，直接匹配virtualDomains.后所有非引号字符，从根源避免贪婪匹配回溯，同时天然不会把末尾引号纳入匹配结果。
2. 给CONNECT BY加循环阻断条件：通过主键关联+SYS_GUID()非空判定，阻断无效递归循环。

修复后可直接运行的SQL：

SELECT regexp_substr(model_view, 'virtualDomains\.[^"]+', 1, LEVEL) AS extracted_value
FROM page
WHERE id = 10815
CONNECT BY LEVEL <= regexp_count(model_view, 'virtualDomains\.[^"]+')
  AND PRIOR id = id
  AND PRIOR SYS_GUID() IS NOT NULL;

执行后会直接返回3条匹配结果，无引号后缀，也不会出现挂起问题。

二、非捕获组(?:"")写法返回空值 #

故障原因 #

Oracle内置正则函数基于POSIX正则标准实现，全版本均不支持非捕获组(?:)、零宽断言这类Perl兼容正则（PCRE）的高级语法，写的非捕获组规则会被Oracle识别为非法分组逻辑，直接导致匹配失效返回空。

修复方案 #

不需要使用非捕获组，两种高效实现方式二选一即可：

• 优先用前面给出的virtualDomains\.[^"]+正则规则，匹配逻辑天然截止到引号前，不会包含末尾引号，性能最优。
• 如果保留原有.*"匹配逻辑，可通过函数截断末尾引号：

SELECT rtrim(regexp_substr(model_view, 'virtualDomains\..*"', 1, 1), '"') AS extracted_value
FROM page
WHERE id = 10815;

注意：编写Oracle正则时不要使用非捕获组、前后预查、命名捕获组等PCRE专属语法，都会出现兼容问题。

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